Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
AntoineG37
Frequent Visitor

Display the latest values by category based on a selected date

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 : 

IdDealIdAsOf DateNotional
1000019/1/2022                                       2000000
1000119/1/2022                                       3000000
1000219/1/2022                                       1818147
1001045/31/2022                                       2000000
1001145/31/2022                                           997500
1001245/31/2022                                       1500000
1001345/31/2022                                       1127820
1001457/27/2022                                           140000
1001557/27/2022                                           210000
1001657/27/2022                                           500000
1001757/27/2022                                           750000
1001857/27/2022                                           625000
1001918/31/2022                                       2000000
1002018/31/2022                                       3000000
1002118/31/2022                                       1801257
1002218/31/2022                                             96976
1002318/31/2022                                       1193365
1002418/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 : 

DealIdSum of Notional
16 818 147
45 625 320
52 225 000
Grand Total14 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) : 

DealIdSum of Notional
18 159 481
45 625 320
52 225 000
Grand Total16 009 800

 

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

DealIdSum of Notional
45 625 320
52 225 000
Grand Total7 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
1 ACCEPTED SOLUTION
daXtreme
Solution Sage
Solution Sage

[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.

View solution in original post

3 REPLIES 3
AntoineG37
Frequent Visitor

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
Solution Sage
Solution Sage

[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.

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"

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.