Formula to calculate date of Monday of previous week

michaelg25

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!

ArborRose

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'

)

Manasi_Panov

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.

ArborRose

How about this

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

michaelg25

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

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'

)

michaelg25

This worked great, thank you so much!

Manasi_Panov

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.

michaelg25

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'))

ArborRose

Try this

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

michaelg25

@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?

ArborRose

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.