cancel
Showing results 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

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 :

 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.

Antoine
1 ACCEPTED SOLUTION
Solution Sage
[Total Notional] =
var LatestVisibleDate = max( Dates[Date] )
var DealsWithLatestDates =
calculatetable(
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.

3 REPLIES 3
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.

Solution Sage
[Total Notional] =
var LatestVisibleDate = max( Dates[Date] )
var DealsWithLatestDates =
calculatetable(
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.

Helper III

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(
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"

Announcements

#### 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 Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors