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 Quick Measures, DAX (Summarize e.t.c) but was unsuccessful : 

 

Coming to the Point : 

 

I have created slicers from this table pasted above : 

 

Slicer 1 : Year

Slicer 2 : Quarter (Populates Quarters of an year when year is selected)

Slicer 3 :  Response Period (1st column Above) - On selection of Quarter / Year this changes accordingly.

 

My Scenario 1 : Annual Average should be constant when i select any of the slicer above (Quarter or Month). The Report looks like below : (Table)

 

 

My Scenario 2 : When i select April, it should display Feb,Mar,April and show average of the 3 months dynamically.

 

 

Earlierst help is appreciated.

 

Thanks,

Nishanth.

 

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

2 Replies

  • 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 :)