Forum Discussion
difference between rolling average and current month
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
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
2 Replies
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!
