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

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! **0 
Hello @michaelg25,
You should be able to do it with HTML Buildin 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.
1
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! **0 
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?0 
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! **0 
This worked great, thank you so much!
0 
Hello @michaelg25,
You should be able to do it with HTML Buildin 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.
1 
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'))0 
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! **0 
@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?
0 
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! **1
Categories
 All Categories
 1.7K Product Ideas
 1.7K Ideas Exchange
 1.5K Connect
 1.2K Connectors
 295 Workbench
 6 Cloud Amplifier
 8 Federated
 2.8K Transform
 97 SQL DataFlows
 608 Datasets
 2.1K Magic ETL
 3.8K Visualize
 2.4K Charting
 709 Beast Mode
 49 App Studio
 39 Variables
 667 Automate
 170 Apps
 446 APIs & Domo Developer
 44 Workflows
 7 DomoAI
 33 Predict
 13 Jupyter Workspaces
 20 R & Python Tiles
 391 Distribute
 111 Domo Everywhere
 274 Scheduled Reports
 6 Software Integrations
 115 Manage
 112 Governance & Security
 Domo Community Gallery
 31 Product Releases
 9 Domo University
 5.3K Community Forums
 40 Getting Started
 30 Community Member Introductions
 103 Community Announcements
 4.8K Archive