How to create a Beast Mode calculation for fiscal year using a date field?
Hi All,
I have a unique dilemma. I need to create a beast mode calculation to calculate our revenue for the latest fiscal year. I know I can just manually add the date filter criteria into the calculation or add a filter but the thing is the data is updated every day and we need the date filter criteria to be dynamic & automated so if I have the data updated today, the date range will be 7/1/2019 - 2/4/2020 and when the data updates tomorrow, the date range should be 7/1/2019 - 2/5/2020 all the way upto fiscal year end. After we transition into next fiscal year, the date range should be reflective.
Our fiscal year 2020 will be July 1, 2019 to June 30, 2020
For the calculation, I was trying
case when YEAR(`date`) =
(case when MONTH(CURRENT_DATE()) <7 then YEAR(CURRENT_DATE()) end )
and MONTH(`date`) in ('1','2','3','4','5','6')
then sum(`revenue`) else 0 end
+
case when YEAR(`date`) =
(case when MONTH(CURRENT_DATE()) >7 then YEAR(CURRENT_DATE()) -1 end )
and MONTH(`date`) in ('7','8','9','10','11','12')
then sum(`revenue`) else 0 end
This isn't working in Domo & keeps giving me 0 value. Can anyone help me with this? I have similar requirements to create beast mode calculations for rolling 12 months, previous year fiscal year, and more so i can't get it to work, it will be a real problem.
Appreciate any help/advice from the community.
Thanks
Best Answer
-
I think there are a couple Domo features worth pointing out before working too hard on a beast mode formula.
First, Domo offers a Fiscal Calendar feature. You just need to e-mail Domo Support to get it enabled and provide them with your fiscal date information. You can read about it here: https://knowledge.domo.com/Visualize/Adding_Cards_to_Domo/KPI_Cards/KPI_Card_Building_Part_2%3A_The_Analyzer/Using_A_Fiscal_Calendar
Second, while not totally automated, you could set the Date Filter to be Greater Than or Equal to 7/1/19 and this work for an entire year regardless of how many times your data is updated.
Third, under the Date Range, you can choose Previous and Last and then enter 12 months. This would get you a rolling 12 months.
If you do want to use a beast mode, this should do it:
(CASE WHEN YEAR(`date`) = YEAR(CURRENT_DATE()) THEN
(CASE WHEN MONTH(`date`) < 7 THEN SUM(`revenue`) ELSE 0 END)
WHEN YEAR(`date`) = YEAR(CURRENT_DATE())-1 THEN
(CASE WHEN MONTH(`date`) >= 7 THEN SUM(`revenue`) ELSE 0 END)
ELSE 0 END)Hope this helps.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.4
Answers
-
I think there are a couple Domo features worth pointing out before working too hard on a beast mode formula.
First, Domo offers a Fiscal Calendar feature. You just need to e-mail Domo Support to get it enabled and provide them with your fiscal date information. You can read about it here: https://knowledge.domo.com/Visualize/Adding_Cards_to_Domo/KPI_Cards/KPI_Card_Building_Part_2%3A_The_Analyzer/Using_A_Fiscal_Calendar
Second, while not totally automated, you could set the Date Filter to be Greater Than or Equal to 7/1/19 and this work for an entire year regardless of how many times your data is updated.
Third, under the Date Range, you can choose Previous and Last and then enter 12 months. This would get you a rolling 12 months.
If you do want to use a beast mode, this should do it:
(CASE WHEN YEAR(`date`) = YEAR(CURRENT_DATE()) THEN
(CASE WHEN MONTH(`date`) < 7 THEN SUM(`revenue`) ELSE 0 END)
WHEN YEAR(`date`) = YEAR(CURRENT_DATE())-1 THEN
(CASE WHEN MONTH(`date`) >= 7 THEN SUM(`revenue`) ELSE 0 END)
ELSE 0 END)Hope this helps.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.4 -
Thanks, it's working now, appreciate it
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 710 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive