Calendar Week Start/End Dates

[greenguy2012]

Hi there,

I'd like to create a column in a calendar table that has the "Calendar Week" start and end dates within a given month (starting on mondays and ending on sundays). I'm not looking for dates that span across different months. For example, 

Date -- Week Start Date -- Week End Date

3/1/2021 -- 3/1/2021 -- 3/7/2021

3/30 -- 3/29 -- 3/30

4/2/ -- 4/1 -- 4/4

We start our work week on mondays and end them on sundays. But we like to see what the start date and end dates are for the week so we can plot them with different volume metrics. 

Any ideas how to do this?

[edhans · Accepted solution]

See if this works for you @greenguy2012 

I don't understand your example above as 3/30/2021 is on a Tuesday, so the start date would be monday, or March 29, and the end would be wednesday, the 31st.
April 1 then is on a thursday, so would be 4/1, and the end of that week would be 4/4, or Sunday.

This is the code:

let
    Source = {Number.From(#date(2021,3,1))..Number.From(#date(2021,5,31))},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Date", type date}}),
    #"Inserted Day Name" = Table.AddColumn(#"Changed Type", "Day Name", each Date.DayOfWeekName([Date]), type text),
    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Day Name", "Day of Week", each Date.DayOfWeek([Date], Day.Monday), Int64.Type),
    WeekStartDate = 
        Table.AddColumn(
            #"Inserted Day of Week",
            "Week Start Date",
            each 
                let
                    varStartDay = Date.StartOfWeek([Date],Day.Monday)
                in
                if Date.StartOfMonth(varStartDay) = Date.StartOfMonth([Date])
                then varStartDay
                else Date.StartOfMonth([Date]),
            Date.Type
        ),
    WeekEndDate = 
        Table.AddColumn(
            WeekStartDate,
            "Week End Date",
            each
                let
                    varEndDay = Date.EndOfWeek([Date], Day.Monday)
                in
                if Date.EndOfMonth(varEndDay) = Date.EndOfMonth([Date])
                then varEndDay
                else Date.EndOfMonth([Date]),
            Date.Type
        )
in
    WeekEndDate

You do NOT need the Day Name or Day of Week number. I used those to see what was going on. What it does is uses the Date.StartOfWeek/Date.EndOfWeek functions forced to use Day.Monday for the first day of the week. If it is in the same month, use the calculation, otherwise use the first/last day of the month to start/end that particular week.

If that isn't right, please provide good data with clear examples per links below.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[v-kelly-msft · Accepted solution]

Hi @greenguy2012 ,

You could also realize it using dax expression:

First create a weeknum column:

Weeknum = WEEKNUM('Table'[Date],2)

Then create 2 columns as below:

Week Start Date = CALCULATE(MIN('Table'[Date]),FILTER('Table','Table'[Weeknum]=EARLIER('Table'[Weeknum])))

Week End Date = CALCULATE(MAX('Table'[Date]),FILTER('Table','Table'[Weeknum]=EARLIER('Table'[Weeknum])))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[edhans · Accepted solution]

See if this works for you @greenguy2012 

I don't understand your example above as 3/30/2021 is on a Tuesday, so the start date would be monday, or March 29, and the end would be wednesday, the 31st.
April 1 then is on a thursday, so would be 4/1, and the end of that week would be 4/4, or Sunday.

This is the code:

let
    Source = {Number.From(#date(2021,3,1))..Number.From(#date(2021,5,31))},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Date", type date}}),
    #"Inserted Day Name" = Table.AddColumn(#"Changed Type", "Day Name", each Date.DayOfWeekName([Date]), type text),
    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Day Name", "Day of Week", each Date.DayOfWeek([Date], Day.Monday), Int64.Type),
    WeekStartDate = 
        Table.AddColumn(
            #"Inserted Day of Week",
            "Week Start Date",
            each 
                let
                    varStartDay = Date.StartOfWeek([Date],Day.Monday)
                in
                if Date.StartOfMonth(varStartDay) = Date.StartOfMonth([Date])
                then varStartDay
                else Date.StartOfMonth([Date]),
            Date.Type
        ),
    WeekEndDate = 
        Table.AddColumn(
            WeekStartDate,
            "Week End Date",
            each
                let
                    varEndDay = Date.EndOfWeek([Date], Day.Monday)
                in
                if Date.EndOfMonth(varEndDay) = Date.EndOfMonth([Date])
                then varEndDay
                else Date.EndOfMonth([Date]),
            Date.Type
        )
in
    WeekEndDate

You do NOT need the Day Name or Day of Week number. I used those to see what was going on. What it does is uses the Date.StartOfWeek/Date.EndOfWeek functions forced to use Day.Monday for the first day of the week. If it is in the same month, use the calculation, otherwise use the first/last day of the month to start/end that particular week.

If that isn't right, please provide good data with clear examples per links below.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.