Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello everyone,
I have a measure (Avg MIS) that calculates an average value over all months in a given context. For example, in the visual below, the 2016 value of 21 is for all of 2016, the 19 value for Q1 is for the three months shown, etc. What I would like to do is get a separate measure (Avg Ending MIS) that shows the average value for the final month in the given context, as shown by the arrows. No matter what date ranges are selected or how they are grouped, the calculated value displayed would always be based on the last date available in the given context.
The _maxdt measure is just a placeholder to ensure that the last date was getting picked up correctly in context.
My measures:
Essentially, this is what i'm hoping to accomplish, but it obviously gives an error:
I expect the solution some combination of FILTERing and/or variables. I've tried all manner of things for Avg Ending MIS, but this is well beyond my limited skillset.
Thanks for any advice you can offer!
Solved! Go to Solution.
@scotjn1,
Please change your DAX to the following:
Avg Ending MIS = var datepara= [_maxdt] return CALCULATE([Avg MIS],FILTER(dates,dates[Bill Date]=datepara))
Regards,
Lydia
@scotjn1,
Please change your DAX to the following:
Avg Ending MIS = var datepara= [_maxdt] return CALCULATE([Avg MIS],FILTER(dates,dates[Bill Date]=datepara))
Regards,
Lydia
Awesome, thanks so much!!! I knew it had to be something like that, but nothing I did was just the right combination!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
87 | |
82 | |
65 | |
64 | |
56 |
User | Count |
---|---|
171 | |
111 | |
109 | |
72 | |
71 |