Latest values by category based on a selected date

Question

First, as I am a French guy, I want to apologise in advance for my poor English! Despite my searches since few days, I cannot find the correct measure to solve my problem. I think I am close to the solution, but I really need help to achieve this job!

Here is my need: I have a dataset with a date table and a "Position" (i.e. "stock") table, which is my fact table, with date column.

Classic relationship between these 2 tables. Many Dates in "Position" table / 1 date un "Dates" table.

My "Dates" table has a one date per day (Column "AsOf")

My "Deals" table looks like this:

Id	DealId	AsOfDate	Notional
10000	1	9/1/2022	2000000
10001	1	9/1/2022	3000000
10002	1	9/1/2022	1818147
10010	4	5/31/2022	2000000
10011	4	5/31/2022	997500
10012	4	5/31/2022	1500000
10013	4	5/31/2022	1127820
10014	5	7/27/2022	140000
10015	5	7/27/2022	210000
10016	5	7/27/2022	500000
10017	5	7/27/2022	750000
10018	5	7/27/2022	625000
10019	1	8/31/2022	2000000
10020	1	8/31/2022	3000000
10021	1	8/31/2022	1801257
10022	1	8/31/2022	96976
10023	1	8/31/2022	1193365
10024	1	8/31/2022	67883

Based on a selected date (slicer with all dates from "Dates" table), I would like to calculate the sum of Last Notional for each "Deal" (column "DealId"). So, I must identify, for each Deal, the last "Asof Date" before or equal to the selected date and sum all matching rows.

Examples: If selected date is 9/1/2022, I will see all rows, except rows asof date = 8/31/2022 for deal 1 (as the last date for this deal is 9/1/2022). So, I expect to see:

DealId  Sum of Notional
1   6 818 147
4   5 625 320
5   2 225 000
Grand Total 14 668 467

If I select 8/31/2022, total for Deal 1 changes (as we now take rows of 8/31 instead of 1/9):

DealId  Sum of Notional
1   8 159 481
4   5 625 320
5   2 225 000
Grand Total 16 009 800

If I select 7/29, only deals 4 and 5 are active on this date, so the results should be:

DealId  Sum of Notional
4   5 625 320
5   2 225 000
Grand Total 7 850 320

I think I found a solution for the rows, but my total is wrong (only notionals of the selected date are totalized). I also think my measure is incorrect if I try to display the notional amounts aggregated by Rating (other column in my table) instead of deal. Here is my measure:

Last Notional =
VAR SelectedAsOf =
    SELECTEDVALUE ( Dates[AsOf] )
VAR LastAsofPerDeal =
    CALCULATE (
        MAX ( Deals[AsOf Date] ),
        FILTER ( ALLEXCEPT ( Deals, Deals[DealId] ), Deals[AsOf Date] <= SelectedAsOf )
    )
RETURN
    CALCULATE (
        SUM ( Deals[Notional] ),
        FILTER (
            ALLEXCEPT ( Deals, Deals[DealId]),
            LastAsofPerDeal = Deals[AsOf Date]
        )
    )

I hope it is clear for you, and you will be able to find a solution for this.

Thanks in advance. Antoine

score 0 · Answer 1 · answered Sep 25 '22 at 09:50

Make sure you have no relationship between your calendar table and deals table like so.

Create a slicer with your dates table and create a table visual with deal id. Then add a measure to the table as follows:

Sum of Notional = 
VAR slicer = SELECTEDVALUE(Dates[Date])
VAR tbl = FILTER(Deals,Deals[AsOfDate] <= slicer) 
VAR maxBalanceDate = CALCULATE(MAX(Deals[AsOfDate]),tbl)


RETURN
CALCULATE(
    SUM(Deals[Notional]),
    Deals[AsOfDate] = maxBalanceDate
)

Latest values by category based on a selected date

1 Answers1