Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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?
Solved! Go to 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @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!
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!
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCheck out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
69 | |
61 | |
18 | |
16 | |
13 |