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.
Hi,
I'm trying to calculate a percentage difference between a last calendar month total and a rolling 6 month average for the same figure. I have a table that contains dates and website hits as columns, and would like to show the following in my dashboard:
'Website hits for previous calendar month' (I'm using relative date filtering to show the value from the last calendar month)
'Website hits - rolling 6 month average' (also displayed using relative date filtering to get last 6 calendar months)
% difference between 'website hits - rolling 6 month average' and 'website hits for previous calendar month
I've tried using a % difference quick measure, but this only seems to allow me to calculate the difference between an aggregate of the website hits - I can't apply the correct date filter to return the value I need.
Any help would be most welcome!
Chris
Solved! Go to Solution.
Hi@ sdas028
You can try to use these three measure as below:
previous calendar month = CALCULATE(SUM(Table1[Qty]),PREVIOUSMONTH('Date'[Date])) rolling 6 month average = CALCULATE(AVERAGE(Table1[Qty]),DATESBETWEEN('Date'[Date],DATEADD(LASTDATE('Date'[Date]),-6,MONTH),LASTDATE('Date'[Date]))) % difference = [rolling 6 month average]/[previous calendar month]
Result:
Here is demo , please try it
Best Regards,
Lin
Hi@ sdas028
You can try to use these three measure as below:
previous calendar month = CALCULATE(SUM(Table1[Qty]),PREVIOUSMONTH('Date'[Date])) rolling 6 month average = CALCULATE(AVERAGE(Table1[Qty]),DATESBETWEEN('Date'[Date],DATEADD(LASTDATE('Date'[Date]),-6,MONTH),LASTDATE('Date'[Date]))) % difference = [rolling 6 month average]/[previous calendar month]
Result:
Here is demo , please try it
Best Regards,
Lin
Thank you Lin, this seems to have worked!
Covering 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 |
---|---|
116 | |
104 | |
77 | |
71 | |
50 |
User | Count |
---|---|
145 | |
108 | |
107 | |
90 | |
65 |