cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors