Display the latest values by category based on a selected date

[AntoineG37]

Hello all,

First, as I am a french guy, I want to apologise in advance for my poor english !

Despite my searches since few days, I can not 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" (ie "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 "Position" table looks like this : 

Id	DealId	AsOf Date	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 clean for you, and you will be able to find a solution for this.

Thanks in advance.

Antoine

[AntoineG37]

Thank you @daXtreme  for your solution. It works perfectly. 

Thank you also for using the TREATAS function. I did not know how to use it.

[daXtreme · Accepted solution]

[Total Notional] =
var LatestVisibleDate = max( Dates[Date] )
var DealsWithLatestDates =
    calculatetable(
        addcolumns(
            values( Position[DealId] ),
            "@LatestDate", 
                calculate( 
                    max( Position[AsOfDate] ),
                    Position[AsOfDate] <= LatestVisibleDate
                )
        ),
        removefilters( Dates )
    )
var Output =
    calculate(
        sum( Position[Notional] ),
        treatas(
            DealsWithLatestDates,
            Position[DealId],
            Position[AsOfDate]
        ),
        removefilters( Dates )
    )
return
    Output

Bear in mind that you should never slice and dice your data by columns in the fact table. Only via dimensions. If you don't stick to this rule... well, be prepared for surprises.

[AlexaderMilland]

Hi, I just posted on this:

https://community.fabric.microsoft.com/t5/Desktop/Latest-value-before-a-selected-date/td-p/3348886

I tried your function but couldn't get it to work for my issue. 
I guess I would need to add the permission at the latest time somewhere in the calculatetable? 
Here is the DAX function rewritten to my sample:
"

PermissionAtDate =

var LatestVisibleDate = max( 'Calendar'[CalendarDate])

var MaxPermissionTime =

    calculatetable(

        addcolumns(

            values( Permissions[CustomerID]),

            "@LatestDateTime",

                calculate(

                    max( 'Permissions'[Date]),

                    Permissions[Date] <= LatestVisibleDate

                )

        ),

        removefilters( 'Calendar' )

    )

var Output =

    calculate(

        DISTINCTCOUNT(Permissions[CustomerID]),

        treatas(

            MaxPermissionTime,

            Permissions[CustomerID],

            Permissions[Date]

        ),

        removefilters( 'Calendar' )

    )

return

    Output"