Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It 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!
User | Count |
---|---|
101 | |
90 | |
80 | |
71 | |
69 |
User | Count |
---|---|
114 | |
97 | |
97 | |
73 | |
72 |