Forum Discussion

Dunner2020's avatar
Dunner2020
Post Prodigy
5 years ago
Solved

Problem with monthly average measure

Hi there,   Sorry for asking for a trivial issue. I have been stuck on the calculation for hours. I have got five years of monthly data. I want to calculate the cumulative sum of the average monthl...
Need Help
Show and Tell
Tips and Tricks
  • amitchandak's avatar
    amitchandak
    5 years ago

    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's avatar
Dunner2020
Post Prodigy
5 years ago

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.

sevenhills's avatar
sevenhills
Super User
5 years ago

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())