How to create a Beast Mode calculation for fiscal year using a date field?

Reply
Yellow Belt

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


Accepted Solutions
Highlighted
Red Belt

Re: How to create a Beast Mode calculation for fiscal year using a date field?

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_...

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.




**Make sure to like any users posts that helped you and accept the ones who solved your issue.**

View solution in original post


All Replies
Highlighted
Red Belt

Re: How to create a Beast Mode calculation for fiscal year using a date field?

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_...

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.




**Make sure to like any users posts that helped you and accept the ones who solved your issue.**

View solution in original post

Highlighted
Yellow Belt

Re: How to create a Beast Mode calculation for fiscal year using a date field?

Thanks, it's working now, appreciate it Robot Happy

Announcements
Win free lodging at Domoapalooza! We want you to share you favorite Domo tips and tricks. Click here for more details!