Filter Month based on Last working Day

RTM · 2025-03-03T12:56:20+00:00

There was an error rendering this rich post.

Today is Monday March 3rd. I am using the following Beast Mode (shared previously in the forums) to filter to current month:

Case 

WHEN 

             DAY(CURRENT_DATE()) = 1 

         AND DATE_FORMAT(`Event Date`,'%m-%Y') = DATE_FORMAT(CURRENT_DATE()-1,'%m-%Y')  

     THEN '00-Current Month'   
WHEN 

             DAY(CURRENT_DATE()) != 1 

         AND DATE_FORMAT(`Event Date`,'%m-%Y') = DATE_FORMAT(CURRENT_DATE(),'%m-%Y')  

     THEN '00-Current Month'
    ELSE DATE_FORMAT(`Event Date`,'%m-%Y')

END

I need a beast mode to filter to the month of the last working day (Monday-Friday work week). So for today I would need it to filter the month to February (last working day being Feb 28th) and not March which is what this current beast mode does. If someone has a beast mode they can share that will do this. That would be appreciated.

Find more posts tagged with

Accepted answers

All comments

DashboardDude

Hi @RTM ,

Trying to understand. So you want to be able to select "current month", but that goes up to the last day of February? Apologies, I'm just not understanding the request, but I bet we can help.

RTM

I want a beast mode that will filter card to month of last working day assuming a Monday to Friday work week. So for Monday March 3rd it should filter to February as the last working day was Friday Feb 28th. On Tuesday March 4th it should filter to March as last working day was Monday March 3rd. On April 1st it should filter to March and on April 2nd it should filter to April. Hopefully that helps explain what I am looking for.

DashboardDude

@RTM

Trying to help, let me know if this gets you closer. I don't think it's as much of a technical difficulty of doing what you want. It's more of a difficulty of me understanding the request, but trying:

https://www.loom.com/share/7a1b2b8a26a64df4ac35943fbd6744cc?sid=93df8072-f080-420b-8b4c-0700b51bc6e8

MarkSnodgrass

As I understand your question, you want to filter your card to the month that the last working day is in. This is a great use case for a nested beast mode as it makes it much cleaner to look at. Here is what I would do:

Create a beast mode called LastWorkingDay with a formula that looks like this:

/* If today is Sunday, subtract 2 days /
CASE WHEN DAYOFWEEK(CURRENT_DATE()) = 1 THEN
DATE_SUB(CURRENT_DATE(), INTERVAL 2 day)
/ If today is Monday, subtract 3 days /
WHEN DAYOFWEEK(CURRENT_DATE()) = 2 THEN
DATE_SUB(CURRENT_DATE(), INTERVAL 3 day)
/ All other days, subtract 1 day */
ELSE
DATE_SUB(CURRENT_DATE(), INTERVAL 1 day)
END

Save and validate and then create a new beast mode that looks like this:

CASE WHEN LAST_DAY(dt) = LAST_DAY(LastWorkingDay) THEN 'Include'
ELSE 'Exclude'
END

Drag this into your filters and set to Include. Also, sent your date range filter last 2 months, which will keep future dates out automatically.

The first function checks to see which day of the week it is and subtracts the necessary days from the current date.

The second function uses that date and compares it to the date from your dataset (dt) and checks to see if they are in the same month and year by using the LAST_DAY function.

Hope this helps.

MarkSnodgrass

Not to be outdone by John 😁, I created a video that walks you through how to do what I explained.

https://youtu.be/pz8dWswu2Cc

RTM

Thank you John and Mark for your feedback. I think both options showed me different options to reach a similar result. Sorry John that I didn't make myself clear enough and I will try to include screenshots if I have any future requests. I work in manufacturing and throughout the month we show results by day. Each morning we review the previous working day results. Current Domo filter was set to 'Current Month by Day'. I want to be able to filter the data without changing the Domo filter. When Monday March 3rd occurred the Domo filter set the card to current month March but for our meetings on the Monday March 3rd we needed to review results from February 28th. The 'it' I was referring to was the card I was using. Thanks again for your answers. I'll try them both to see which works when April roles around