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