Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
jwkuyper
Frequent Visitor

Using last date in Date Slicer Range to obtain value

 

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

1.JPG

I have this table.

2.JPG

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: 

3.JPGthis 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
AllisonKennedy
Super User
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. 

 

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


Please @mention me in your reply if you want a response.

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.
If you found this post helpful, please give Kudos C

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

View solution in original post

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) 

 

View solution in original post

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:

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

 

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


Please @mention me in your reply if you want a response.

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.
If you found this post helpful, please give Kudos C

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

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) 

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors