Forum Discussion
Problem with monthly average measure
Dunner2020 , then you need to RY Month NO
Monthly_average_measure = calculate(Averagex(values(Date['Month']),[sum_measure]), allexcept(Date,Date[RY Month NO]))
better would be
Monthly_average_measure = calculate(Averagex(values(Date['Month']),[sum_measure]), filter(allselected(Date),Date[RY Month NO])=max(Date[RY Month NO]))
Dunner2020 , fi you take only yeat then if will give Monthly Average. Else use allexpect for year and try
Monthly_average_measure = calculate(Averagex(values(Date['Month']),[sum_measure]), allexcept(Date,Date[Year]))
amitchandak , When I used your formula, it produces same monthly average as shown below:
I am expecting that same month should have same value. For example, the monthly average of April in 2016 should be the same as in April 2017 because its monthly average of 5 years.
Dunner2020 , then you need to RY Month NO
Monthly_average_measure = calculate(Averagex(values(Date['Month']),[sum_measure]), allexcept(Date,Date[RY Month NO]))
better would be
Monthly_average_measure = calculate(Averagex(values(Date['Month']),[sum_measure]), filter(allselected(Date),Date[RY Month NO])=max(Date[RY Month NO]))
will this work?
Monthly avg_measure 2 = IF( SUM('5years'[Value]) , CALCULATE( AVERAGEX( VALUES(Dates[RY Month Number]), SUM('5years'[Value]) ), ALLEXCEPT(Dates, Dates[RY Month Number])), blank())
