cancel
Showing results for
Did you mean:
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
Super User
``````[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.

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

Super User
``````[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.

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors