Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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!
Solved! Go to 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/
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)
// 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
@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/
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)
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
15 | |
11 | |
7 |
User | Count |
---|---|
25 | |
24 | |
12 | |
12 | |
11 |