Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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êsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |