Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Anonymous
Not applicable

Critical and Urgent! Need help Rolling average calculation

My Data looks as below : 

 

image.png

 

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)

 

image.png

 

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 🙂

View solution in original post

2 REPLIES 2
v-jiascu-msft
Microsoft Employee
Microsoft Employee

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

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 🙂

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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