Variance from Historical Average

mcrandall

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.

 

 

 

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.

 

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.

 

mcrandall

Thanks for the help, this works perfect. To add to it we also updated the beast mode to divide by unique months instead of a set number. This allows my card builders to use the normal date filters in the card or page and have the comparison of "vs. Last # Months" period automatically adjust accordingly.