difference between rolling average and current month

[ChrisB1977]

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

[v-lili6-msft · Accepted 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

https://www.dropbox.com/s/wjelg4gpq3y1xno/difference%20between%20rolling%20average%20and%20current%2...

Best Regards,

Lin

[ChrisB1977]

Thank you Lin, this seems to have worked!