Calendar Week Start/End Dates

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.

 

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!