cancel
Showing results for 
Search instead for 
Did you mean: 
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
Super User
Super User

[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

2 REPLIES 2
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
Super User
Super User

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

Helpful resources

Announcements
Carousel_PBI_Wave1

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.

Power BI Summit Carousel 2

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!

Thank you 2022 Review

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