Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Critical and Urgent! Need help Rolling average calculation

My Data looks as below :      My Query :    I need to calculate annual average and also Rolling average of last 3 months. I used several ways of doing this availble in the blog, through Qu...
Data Modeling
  • Anonymous's avatar
    Anonymous
    8 years ago

    Thanks for the update Dale. Somehow, this doesn't work for my data as slicer selection should not affect this data. I have worked on a different solution by summarizing my actual table into a new table. But this explanation given by you has given me a better idea towards calculation. 

     

    Thank you :)

v-jiascu-msft's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi Nishanth.,

 

I guess your data looks like this:

Critical_and_Urgent_Need_help_Rolling_average_calculation1

So, the last four columns are necessary. This mode needs a date table that will provide all date attributes. We need to unpivot these data to make it easy to handle. Please check out the demo here.

Critical_and_Urgent_Need_help_Rolling_average_calculation2

Create a measure like this:

avg_last3months =
CALCULATE (
    AVERAGE ( Table1[Value] ),
    DATESINPERIOD ( 'Calenar'[Date], MAX ( 'Calenar'[Date] ), -3, MONTH ),
    ALL ( Table1[Attribute] )
)
Critical_and_Urgent_Need_help_Rolling_average_calculation3

Best Regards,

Dale

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Thanks for the update Dale. Somehow, this doesn't work for my data as slicer selection should not affect this data. I have worked on a different solution by summarizing my actual table into a new table. But this explanation given by you has given me a better idea towards calculation. 

 

Thank you :)