Help with Date Hierarchy - Don't want to aggregate months and Instead want the last month Value

[Connoisseur]

Hi Experts,

Thanks for taking time to read through this post. Much appreciated

I have got a date table ( Year - Quater - Month - Date Hierarchy).While using it in matrix visualization I am just using Year and Month as shown below

Since headcount is in the value field, I dont want it to get aggregated rather I want the last available month value should get rolled up to year

For example: In the below I want 2017 to have 2 since the headcount in December is 2

However I am getting 12, which is sum of all months value

Please guide me , How can I show the value of the last available month of the particular year instead of aggregating all the months

Thanks and Regards,

Connoisseur

[MFelix]

Hi @Connoisseur ,

I don't know how you have you data setup in terms of dates (if there is a single value per date) but you should use a measure similar to this one:

Measure = CALCULATE(SUM('Table'[Column1]);FILTER(ALL('Table'[date]);'Table'[date] = MAX('Table'[date])))

I'm picking up the values for the maximum date in the context of the measure in your case will pick up the maximum date in 2017 and return the sum of those values.

As refered don't know how your model is setup but if you have a line per each person you may need to make a count and change the filter for month insted of specific date.