Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I'm trying to calculate 7 day rolling average. The image shows the result calculated in Excel. Filtering the data using the two slicers would adjust the sum and hence the rolling average. Excel file with raw data is available at the below link:
https://drive.google.com/file/d/1B8Wqvh9CUeCz0uRV_q813BmLk04NQvq0/view
Can someone please help achieve this in PBI? Any help would be greatly appreciated.
Solved! Go to Solution.
Hi @Anonymous,
Try the following measure:
Rolling Average = CALCULATE ( AVERAGEX ( FILTER ( SUMMARIZE ( ALL ( Raw_Data[Date]; Raw_Data[Leads] ); Raw_Data[Date]; "Leads_Sums"; SUM ( Raw_Data[Leads] ) ); Raw_Data[Date] >= MAX ( Raw_Data[Date] ) - 6 && Raw_Data[Date] <= MAX ( Raw_Data[Date] ) ); [Leads_Sums] ) )
Should work if you add the categories to your filters or visuals also.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi,
You may download my PBI file from here.
Hope this helps.
Thanks for the effort. Your solution works too, however, it breaks if I use Cat1 or Cat2 in the visual. It keeps working if i use them as filters though.
Hi,
Could you show that in a screenshot please?
@Anonymous : Try this measure
7Day Rolling Avg := VAR sevendates = DATESBETWEEN ( Data[Date], MIN ( Data[Date] ) - 6, MIN ( Data[Date] ) ) RETURN IF ( [Total Leads] <> BLANK () && COUNTROWS ( sevendates ) = 7, DIVIDE ( CALCULATE ( [Total Leads], sevendates ), COUNTROWS ( sevendates ) ) )
Here is the result
Ideally you should also create a calendar table and drive the measure from there.
Thanks
Hi @Anonymous,
Try the following measure:
Rolling Average = CALCULATE ( AVERAGEX ( FILTER ( SUMMARIZE ( ALL ( Raw_Data[Date]; Raw_Data[Leads] ); Raw_Data[Date]; "Leads_Sums"; SUM ( Raw_Data[Leads] ) ); Raw_Data[Date] >= MAX ( Raw_Data[Date] ) - 6 && Raw_Data[Date] <= MAX ( Raw_Data[Date] ) ); [Leads_Sums] ) )
Should work if you add the categories to your filters or visuals also.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
90 | |
74 | |
67 | |
63 | |
55 |
User | Count |
---|---|
99 | |
88 | |
72 | |
60 | |
59 |