Formula to calculate date of Monday of previous week

Hello!
I have a card that is filtered to the previous week. In the title of the card, or in a summary number within the card, I'm trying to show the start date of the data. Something like:

"Card Title | Week of 9/9/2024"

And the date portion of that title would be automated. Every week when the card details change, that title would update with last weeks date for Monday. I couldn't get that to work with the 'Smart Text' Options within the cart title options. So I've also tried to use a beast mode and just use the Summary Number options. But I couldn't get the beast mode to work either. I've tried this (and a few other variations), but no luck yet:

Attempt #1:

DATE_ADD(CURRENT_DATE(), INTERVAL -7 - WEEKDAY(CURRENT_DATE()) DAY)

Attempt #2:

DATE_SUB(CURRENT_DATE(), INTERVAL (WEEKDAY(CURRENT_DATE()) + 7) DAY)

Attempt #2 was closer, but returning the wrong date consistently. Any help here would be great!

Best Answers

  • ArborRose
    ArborRose Coach
    Answer βœ“

    When you create a Beast Mode formula that involves window functions like COUNT() OVER() or certain date calculations, it might not appear in card titles, summary numbers, or filters because those sections may only accept simple aggregations.

    It might take it as a string:

    DATE_FORMAT(
    DATE_SUB(CURRENT_DATE(), INTERVAL (CASE WHEN DAYOFWEEK(CURRENT_DATE()) = 2 THEN 7 ELSE DAYOFWEEK(CURRENT_DATE()) - 2 END) DAY),
    '%Y-%m-%d'
    )

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • Manasi_Panov
    Manasi_Panov Contributor
    Answer βœ“

    Hello @michaelg25,

    You should be able to do it with HTML Build-in Summary number like:

    CONCAT('As of ', DATE_SUB(CURRENT_DATE(), INTERVAL (CASE WHEN DAYOFWEEK(CURRENT_DATE()) = 2 THEN 7 ELSE DAYOFWEEK(CURRENT_DATE()) - 2 END) DAY), ' Total amount is ', SUM(1))

    Change SUM(1) with any calculation you want to see as a summary.

    If you found this post helpful, please use πŸ’‘/πŸ’–/πŸ‘/😊 below! If it solved your problem, don't forget to accept the answer.

Answers

  • How about this

    DATE_SUB(CURRENT_DATE(), INTERVAL (CASE WHEN DAYOFWEEK(CURRENT_DATE()) = 2 THEN 7 ELSE DAYOFWEEK(CURRENT_DATE()) - 2 END) DAY)
    

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • Hey @ArborRose - that worked! It seems to be returning the date of monday of last week, thank you!
    The issue now is I need to include this number in the card title or the summary number of the card - but this beast mode does not show up as an option.

    Would you (or anyone) have insight as to how to change that and make it available?

  • ArborRose
    ArborRose Coach
    Answer βœ“

    When you create a Beast Mode formula that involves window functions like COUNT() OVER() or certain date calculations, it might not appear in card titles, summary numbers, or filters because those sections may only accept simple aggregations.

    It might take it as a string:

    DATE_FORMAT(
    DATE_SUB(CURRENT_DATE(), INTERVAL (CASE WHEN DAYOFWEEK(CURRENT_DATE()) = 2 THEN 7 ELSE DAYOFWEEK(CURRENT_DATE()) - 2 END) DAY),
    '%Y-%m-%d'
    )

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • This worked great, thank you so much!

  • Manasi_Panov
    Manasi_Panov Contributor
    Answer βœ“

    Hello @michaelg25,

    You should be able to do it with HTML Build-in Summary number like:

    CONCAT('As of ', DATE_SUB(CURRENT_DATE(), INTERVAL (CASE WHEN DAYOFWEEK(CURRENT_DATE()) = 2 THEN 7 ELSE DAYOFWEEK(CURRENT_DATE()) - 2 END) DAY), ' Total amount is ', SUM(1))

    Change SUM(1) with any calculation you want to see as a summary.

    If you found this post helpful, please use πŸ’‘/πŸ’–/πŸ‘/😊 below! If it solved your problem, don't forget to accept the answer.

  • michaelg25
    michaelg25 Member
    edited September 24

    Hey @Manasi_Panov & @ArborRose - so yesterday (on a monday) the formula was returning 9/16, which was the date on monday of last week, which is exactly what i needed. But now, on a tuesday, the formula is returning 9/23 (the day of monday this week, which i guess is the most recent previous monday?).

    But i need this formula to return 9/16 (or last week's date for Monday) until 9/29 - and then on 9/30 the formula should switch and return 9/23.

    Does that make sense? I don't even know if that's possible. But I just need a formula to return last week's monday date all week, until we get to next weeks monday.

    Any help here would be great. thank you in advance! (but @Manasi_Panov - the concat and formatting you suggested works great!)

    This is what I'm currently using:


    CONCAT('Week of ', DATE_FORMAT(
    DATE_SUB(CURRENT_DATE(), INTERVAL (CASE WHEN DAYOFWEEK(CURRENT_DATE()) = 2 THEN 7 ELSE DAYOFWEEK(CURRENT_DATE()) - 2 END) DAY),
    '%m-%d-%Y'))

  • Try this

    DATE_SUB(CURRENT_DATE(), INTERVAL (CASE WHEN DAYOFWEEK(CURRENT_DATE()) = 2 THEN 7 ELSE DAYOFWEEK(CURRENT_DATE()) + 5 END) DAY)

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • @ArborRose - that seems to work! thank you! so you believe that as the week moves on, and the dates change, that formula will continue to return 9/16 until monday next week?

  • Explanation:

    DAYOFWEEK(CURRENT_DATE()) = 2 is Monday. If it's Monday, we subtract 7 days to get the previous Monday (e.g., on Monday, 9/30, this will return 9/23).

    For any other day (Tuesday through Sunday), we subtract the number of days since last Monday. The DAYOFWEEK(CURRENT_DATE()) + 5 handles this by calculating how far back to go to the previous Monday.

    β€’ For example, on Tuesday, DAYOFWEEK(CURRENT_DATE()) = 3, so this will subtract 8 days (returning the Monday of the previous week).

    β€’ On Sunday, it will subtract 12 days to go back to the Monday of the previous week.

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **