cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Using last date in Date Slicer Range to obtain value

I have this slicer, which is based off of a date hierarchy.

I have this table.

As you can see I also have the following measure that is calculating the last date in the above slicer's selected range. Also to prove that my measure is correct, I have included the column 'Latest Month End Date' to show that my measure and the last date in the slicer are matching.

``````Max_Quarter_Value =
MAXX(
KEEPFILTERS(VALUEs('AUM Source'[Month End Date])),
CALCULATE(MAX('AUM Source'[Month End Date]))
)``````

My issue is -- I want to show 'End AUM Test' as the ending AUM for that last period in the selected range, '09/30/2020' in this case. Right now 'End AUM test' is summing all quarters from the above selecected range. As you can see from the below card:

this summed will equal \$621B matching table above

My current code for 'End AUM Test' is below.

``````End AUM Test =
CALCULATE(
SUM('AUM Source'[Value]),
Filter(
'AUM Source',
and(
'AUM Source'[ShortAcctName]="End AUM",
'AUM Source'[Month End Date].[Date]=[Max_Quarter_Value]
)))``````

Thank you!

2 ACCEPTED SOLUTIONS
Super User

@jwkuyper  A couple of things you can play with.

Try using LASTDATE instead of MAX.

Careful with your DAX context. You may need to look at using EARLIER or Variables to hard code that Max Quarter Value into the calculation, otherwise it is free to change with the context of the FILTER function it is nested within.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Frequent Visitor

This was very good advice. I used LASTDATE instead of MAX and also created a variable within my measure as you recommended. This works, thank you.

``````End AUM Based on Last Date in Slicer =

VAR
EndDate = LASTDATE('AUM Source'[Month End Date])
Return

CALCULATE(
sum('AUM Source'[End AUM]),
'AUM Source'[Month End Date] = EndDate) ``````

4 REPLIES 4
Anonymous
Not applicable
``````// If you create a correct model, then your calculation will be
// also correct. For a correct model you need:
// 1. An Account dimension.
// 2. A proper Calendar dimension.
// 3. A fact table (AUM Source) that joins to them.
// All columns in a fact table must always be hidden and
// slicing must take place via dimensions only.
// Then your measure is this:

End AUM =
CALCULATE(
SUM( 'AUM Source'[Value] ),
KEEPFILTERS( Account[Short Acct Name] = "End AUM" ),
LASTDATE( 'Calendar'[Date] )
)``````

Best

D

Anonymous
Not applicable
By the way, your measure above is wrong for many reasons (in fact, too many to talk about here) but the main is that you don't seem to understand what "context transition" is and when it happens. You are getting a sum for the whole period selected because measures, when calculated by iterators, perform context transition. As a result, [Max_Quarter_Value] on each row of 'AUM Source' has exactly the same value as [Month End Date].[Date].

My advice to you is this. Please learn about DAX and stick to the proper dimensional modelling principles. You'll save yourself countless hours of grief and frustration. Here's something to get you started on your way to understanding DAX and data modelling:

And here's something about proper time-intel:

https://www.sqlbi.com/tv/time-intelligence-in-microsoft-power-bi/

Best
D
Super User

@jwkuyper  A couple of things you can play with.

Try using LASTDATE instead of MAX.

Careful with your DAX context. You may need to look at using EARLIER or Variables to hard code that Max Quarter Value into the calculation, otherwise it is free to change with the context of the FILTER function it is nested within.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Frequent Visitor

This was very good advice. I used LASTDATE instead of MAX and also created a variable within my measure as you recommended. This works, thank you.

``````End AUM Based on Last Date in Slicer =

VAR
EndDate = LASTDATE('AUM Source'[Month End Date])
Return

CALCULATE(
sum('AUM Source'[End AUM]),
'AUM Source'[Month End Date] = EndDate) ``````