Critical and Urgent! Need help Rolling average calculation

[Anonymous]

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.

[v-jiascu-msft]

Hi Nishanth.,

I guess your data looks like this:

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.

Create a measure like this:

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

Best Regards,

Dale

[Anonymous · Accepted solution]

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 🙂