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

mamedu
mamedu Member
in Magic ETL

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

Tagged:

Best Answer

  • MarkSnodgrass
    MarkSnodgrass Coach
    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 <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach
    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 <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • mamedu
    mamedu Member

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

Categories