cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
greenguy2012
Helper I
Helper I

Calendar Week Start/End Dates

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?

2 ACCEPTED SOLUTIONS
edhans
Super User
Super User

See if this works for you @greenguy2012 

edhans_0-1615423282145.png

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.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

v-kelly-msft
Community Support
Community Support

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:

v-kelly-msft_0-1615793650547.png

 

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

View solution in original post

2 REPLIES 2
v-kelly-msft
Community Support
Community Support

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:

v-kelly-msft_0-1615793650547.png

 

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

View solution in original post

edhans
Super User
Super User

See if this works for you @greenguy2012 

edhans_0-1615423282145.png

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.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors