Using last date in Date Slicer Range to obtain value

[jwkuyper]

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!

[AllisonKennedy · Accepted solution]

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

Here's a good intro to variables to help you out: https://www.sqlbi.com/articles/variables-in-dax/

[Anonymous]

// 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]

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:

https://www.youtube.com/watch?v=_quTwyvDfG0
https://www.youtube.com/watch?v=78d6mwR8GtA

And here's something about proper time-intel:

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

Hope it'll help you.

Best
D

[AllisonKennedy · Accepted solution]

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

Here's a good intro to variables to help you out: https://www.sqlbi.com/articles/variables-in-dax/

[jwkuyper · Accepted solution]

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)