calculate a measure only for the last/final date in the current context

[scotjn1]

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:

Avg MIS = AVERAGE(estimates[calc_MIS])

_maxdt = MAX(dates[Bill Date])

Essentially, this is what i'm hoping to accomplish, but it obviously gives an error:

Avg Ending MIS = CALCULATE([Avg MIS],dates[Bill Date]=[_maxdt])

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!

[v-yuezhe-msft · Accepted 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]

Awesome, thanks so much!!!  I knew it had to be something like that, but nothing I did was just the right combination!