Variance from Historical Average

I'm looking to create a card that shows the percent and/or change in a value for last month compared to the last 12 month average. So if last month there was $1M in revenue but the last 12 months averaged $1.2M per month in revenue, I want to show -16.7%. This would be very similar to the "Compare" functionality on period-over-period charts, however that's limited to the last 1-4 months specifically or the same time last year.

My intent is to ultimately establish thresholds for conditional formats and alerts to more proactively notify department owners of these variances so I'm open to suggestions if if there's also a different way to accomplish the same goal.

Find more posts tagged with

Accepted answers

BlueRooster

How are you wanting to display this? Also, are you wanting to include the 'Last Month' in your 12 month average or is this 12 months prior to your 'Last Month', I'll assume the later.

You could try something like this.

Last Month Revenue:

SUM(CASE WHEN `Date` >= DATE_ADD(LAST_DAY(DATE_SUB(CURRENT_DATE(), INTERVAL 2 MONTH)), INTERVAL 1 DAY)
AND
`Date` <= LAST_DAY(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH))
THEN `Revenue`
ELSE 0
END)

Previous 12 Month Avg Revenue:

SUM( CASE WHEN `Date` > DATE_SUB(LAST_DAY(DATE_SUB(CURRENT_DATE(), INTERVAL 2 MONTH)), INTERVAL 1 YEAR)
AND `Date` <=
LAST_DAY(DATE_SUB(CURRENT_DATE(), INTERVAL 2 MONTH))
THEN `Revenue`
ELSE 0 
END)
/ 12

Using those two you can calculate variance by (Last Month / Previous Year Avg) - 1

Hopefully that get's you closer to your goal. Let me know if you have any questions,

Valiant_Ronin

**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.

All comments