cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## 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.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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 2
Microsoft Employee

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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 🙂

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors