Domo Ideas Exchange - Beast Modes - First / Last Days of the Month / Week

GrantSmith
GrantSmith Coach
edited March 2023 in Beast Mode

Problem:

How Do I calculate the start or end date of the week or month?


Solution:

Last Day of the Month:

This is fairly simple as we can use utilize the LAST_DAY function which will return the last day of the month for the provided date.

LAST_DAY(`dt`)

First Day of the Month:

For this we can do some simple math to subtract a specific number of days. We need to get back to day one. The DAYOFMONTH function will return the day number of the supplied date. We just need to subtract one less from the date's day number

-- Author: 
-- Created: 
-- Last Modified: 
-- Description: Subtract the number of days of the date (except one) to get the first of the month
-- 24-(24-1) => day 24 - 23 days = day 1
DATE_SUB(`dt`, INTERVAL (DAYOFMONTH(`dt`) - 1) DAY)

Last Day of the Week:

Similar to the first day of the month we can utilize math to add a specific number of days to get to Saturday. There is a function called DAYOFWEEK which will return a number between 1 (Sunday) and 7 (Saturday). We simply need to calculate the difference from the given date to Saturday and add that number of days.

-- Author:
-- Created:
-- Last Modified:
-- Description: Get the last day of the week.
-- This is done by getting the day of the week (1-7) and adding the number
-- of days needed to equal 7 (Saturday)
DATE_ADD(`dt`, INTERVAL (7- DAYOFWEEK(`dt`)) DAY)

First Day of the Week:

For the First day of the week we do the opposite of the Last Day of the Week and subtract days until we get to day 1 of the week.

-- Author:
-- Created:
-- Last Modified:
-- Description: Get the first day of the week
-- This is done by subtracting the day number (1-7) from the given date.
-- 5 (Thursday) - (5-1) days => 5 - 4 = 1 (Sunday)
DATE_SUB(`dt`, INTERVAL (DAYOFWEEK(`dt`) - 1) DAY)
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**

Comments

  • Derreck
    Derreck Member

    @GrantSmith What if you would like to have Monday as the first day of the week?

  • GrantSmith
    GrantSmith Coach

    Hi @Derreck

    You can just add a day to the date:

    DATE_ADD(DATE_SUB(`dt`, INTERVAL (DAYOFWEEK(`dt`) - 1) DAY), INTERVAL 1 DAY)

    End of the week:

    DATE_ADD(`dt`, INTERVAL (7- DAYOFWEEK(`dt`) + 1) DAY)


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

    @GrantSmith


    Thank you!

  • Hiraayub
    Hiraayub Member

    How to get a last day of the next month?

  • GrantSmith
    GrantSmith Coach

    @Hiraayub 

    LAST_DAY(DATE_ADD(`dt`, INTERVAL 1 MONTH))
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • vikrao
    vikrao Member

    Thanks Grant! This was very helpful.

  • RickM
    RickM Member

    Hi Grant (or anyone else who can assist),

    I'm running into an issue attempting to change day of week to Monday when I do the following (per your instructions above):

    1. DATE_ADD(DATE_SUB(`Create Date`, INTERVAL (DAYOFWEEK(`Create Date') - 1) DAY), INTERVAL 1 DAY)

    It does appear to work, but my corresponding rows don't change. It's as if I'm changing what shows for that cell to be this date, but the actual data hasn't changed. I can tell this if I also show the full date next to it, it loops in previous week's days into that current week that I'm setting it as. You can see this in the screenshot below (created date is the same data used in week start date).


    image.png


    Please help.

  • quincy
    quincy Member

    Hi

    Hopefully this is helpful...

    I riffed on some of the other answers provided and came up with this which works for me to create a week commencing monday field

    -- Author:
-- Created:
-- Last Modified:
-- Description: Returns the previous monday date
-- Use case for creating week commencing on Monday weeks


CASE 
	WHEN 
    	DAYOFWEEK(`Date`) >= 3 
        THEN DATE_SUB(`Date`, INTERVAL (DAYOFWEEK(`Date`) -2) DAY)
    WHEN 
    	DAYOFWEEK(`Date`) = 2 
        THEN `Date`
    WHEN 
    	DAYOFWEEK(`Date`) = 1 
        THEN DATE_SUB(`Date`, INTERVAL 6 DAY)
	ELSE
    -1
    
    END
  • DHo
    DHo Member

    Maybe I'm missing something or rules have changed in Domo since this was posted …

    For first of the month, I don't think there's a need for the date_sub fanciness, just a simple date > concat.

    date(
    
  concat(
    
    year( date_col ), '-',
    
    month( date_col ), '-01' 
    ) )

  • GrantSmith
    GrantSmith Coach

    Thanks for sharing @DHo. That is another way to get the first of the month. Either one will work, I think it’s just a matter of preference. Yours is probably more clear than how I wrote mine. I was in a more arithmetic mindset when I wrote my version. Appreciate the input!

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

Categories