Beastmode For Prior Month, Current Month, Next Month, Month +2

ARutledge · June 4

I currently have 4 different cards that show me the prior month forecast by week, current month forecast by week, next month forecast by week and 2 month ahead forecast by week. All 4 cards show me the current week price forecast, what the price forecast was last week, and the variance between what showed last week and this week. I would like to combine all of those cards into one but I'm having trouble figure out a beastmode that would filter the dates properly for me. I currently just filter the dates on each card to show what date range I'm looking for.

My thought is I'll need to create 12 different Beastmodes to filter for all of the data/dates I need:

Last Week:

-Prior Month

-Current Month

-Next Month

-Month +2

Current Week:

-Prior Month

-Current Month

-Next Month

-Month +2

Variance Between last week and this week:

-Prior month

-Current month

-Next month

-Month +2

Data_Devon · June 4

Oh boy. I've been struggling with this too for a while. There's a few really good write ups to get you started, so I won't claim to know the answer but rather point to those who do:

https://community-forums.domo.com/main/discussion/53481/a-more-flexible-way-to-do-period-over-period-comparisons#latest

https://community-forums.domo.com/main/discussion/comment/101876#Comment_101876

Your thought process is correct, but it'll be more scalable and responsible to join on a Date Dimensions table and then have restructured WIDE data to work with. Both the above will go into detail on this. I'm afraid there isn't an easy answer though.

God Speed 🫡

ArborRose · June 4

forecast_date	run_date	forecast_value
2025-07-15	2025-06-03	$45.00
2025-07-15	2025-05-27	$43.50

Month Category (which month)

CASE 

  WHEN MONTH(`forecast_date`) = MONTH(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH))

       AND YEAR(`forecast_date`) = YEAR(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH)) THEN 'Prior Month'

  WHEN MONTH(`forecast_date`) = MONTH(CURRENT_DATE())

       AND YEAR(`forecast_date`) = YEAR(CURRENT_DATE()) THEN 'Current Month'

  WHEN MONTH(`forecast_date`) = MONTH(DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH))

       AND YEAR(`forecast_date`) = YEAR(DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH)) THEN 'Next Month'

  WHEN MONTH(`forecast_date`) = MONTH(DATE_ADD(CURRENT_DATE(), INTERVAL 2 MONTH))

       AND YEAR(`forecast_date`) = YEAR(DATE_ADD(CURRENT_DATE(), INTERVAL 2 MONTH)) THEN 'Month +2'

  ELSE 'Other'

END

Week Category (forecast was run this week or last week)

CASE

  WHEN WEEK(`run_date`) = WEEK(CURRENT_DATE()) AND YEAR(`run_date`) = YEAR(CURRENT_DATE()) THEN 'Current Week'

  WHEN WEEK(`run_date`) = WEEK(DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)) 

       AND YEAR(`run_date`) = YEAR(DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)) THEN 'Last Week'

  ELSE 'Other'

END

Variance

`this_week_forecast` - `last_week_forecast`

And a pivot card to produce an output

forecast_date	Month Category	Current Week Forecast	Last Week Forecast	Variance
2025-07-15	Next Month	$45.00	$43.50	$1.50

ARutledge · June 5

thank you for the help everyone!

Beastmode For Prior Month, Current Month, Next Month, Month +2

Answers

Categories