Filter Month based on Last working Day

RTM
RTM Member
in Beast Mode

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.

Answers

  • DashboardDude
    DashboardDude Coach

    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.

    Circle.png

    John Le

    Are you on my newsletter? If not, signup here so you don't miss out on my Domo tricks, alerts about my webinars, cooking tips and more

    Signup here:

    https://www.dashboarddudes.com/newsletter

  • RTM
    RTM Member

    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
    DashboardDude Coach

    @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

    Circle.png

    John Le

    Are you on my newsletter? If not, signup here so you don't miss out on my Domo tricks, alerts about my webinars, cooking tips and more

    Signup here:

    https://www.dashboarddudes.com/newsletter

  • MarkSnodgrass
    MarkSnodgrass Coach

    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.

    **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.
  • MarkSnodgrass
    MarkSnodgrass Coach

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

    https://youtu.be/pz8dWswu2Cc
    **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.
  • RTM
    RTM Member

    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

    image.png

Categories