Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

How to get a 12-month rolling average

Sorry, I know this is a fairly common question, but the solutions I've seen don't seem to be working out.    The data is set up like this. Each month, there is a goal. If the goal is met, then the ...
Need Help
  • amitchandak's avatar
    amitchandak
    3 years ago

    Anonymous , You need to try like

    You should always use date table for time intelligence


    CALCULATE(
    AVERAGEX(values(Date[Month year]) ,calculate( Sum(table[goal_reached])) ),
    DATESINPERIOD(Date[Date], LASTDATE(Date[month]), -12, MONTH) )


     

     

    or

     


    CALCULATE(
    AVERAGEX(values(Date[Month year]) ,calculate( AVERAGE(table[goal_reached])) ),
    DATESINPERIOD(Date[Date], LASTDATE(Date[month]), -12, MONTH) )

     

     

    Rolling Months Formula: https://youtu.be/GS5O4G81fww

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
3 years ago

Anonymous , You need to try like

You should always use date table for time intelligence


CALCULATE(
AVERAGEX(values(Date[Month year]) ,calculate( Sum(table[goal_reached])) ),
DATESINPERIOD(Date[Date], LASTDATE(Date[month]), -12, MONTH) )


 

 

or

 


CALCULATE(
AVERAGEX(values(Date[Month year]) ,calculate( AVERAGE(table[goal_reached])) ),
DATESINPERIOD(Date[Date], LASTDATE(Date[month]), -12, MONTH) )

 

 

Rolling Months Formula: https://youtu.be/GS5O4G81fww

Millar's avatar
Millar
Frequent Visitor
2 years ago

How we should calculate 12 month rolling average if my column values in alphanumeric