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.
Best Answer
-
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)
/ 12Using 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.2
Answers
-
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)
/ 12Using 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.2 -
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.
1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive