Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hello,
In Excel it is real easy to create friendly week names such as:
From Jul 9 to Jul 15 |
From Jul 16 to Jul 22 |
From Jul 30 to Aug 5 |
From Aug 6 to Aug 12 |
From Aug 13 to Aug 19 |
From Aug 20 to Aug 26 |
From Aug 27 to Sep 2 |
From Sep 3 to Sep 9 |
Is there any way to do this in Power BI from a Date field?
Solved! Go to Solution.
Hi @lcasey
In Queries Editor,
Add custom columns
weeknum=Date.WeekOfYear([date], Day.Monday)
start of week=Date.StartOfWeek([date], Day.Monday)
end of week=Date.EndOfWeek([date], Day.Monday)
Click on column [date] column, select "Add column"->Date, insert "Name of Month"
Click on column [start of week] column, select "Add column"->Date, insert "Day"
Click on column [end of week] column, select "Add column"->Date, insert "Day"
Click on column [Month Name] and [Day], select "Add column"->"Merge columns" (separator:space)
Click on column [Month Name] and [Day.1], select "Add column"->"Merge columns" (separator:space)
Add a custom column
Custom=Text.Combine({"From", [Merged],"to",[Merged.1]}, " ")
Then ypu could remove other columns, only keep the last column
Best Regards
Maggie
Hi @lcasey
In Queries Editor,
Add custom columns
weeknum=Date.WeekOfYear([date], Day.Monday)
start of week=Date.StartOfWeek([date], Day.Monday)
end of week=Date.EndOfWeek([date], Day.Monday)
Click on column [date] column, select "Add column"->Date, insert "Name of Month"
Click on column [start of week] column, select "Add column"->Date, insert "Day"
Click on column [end of week] column, select "Add column"->Date, insert "Day"
Click on column [Month Name] and [Day], select "Add column"->"Merge columns" (separator:space)
Click on column [Month Name] and [Day.1], select "Add column"->"Merge columns" (separator:space)
Add a custom column
Custom=Text.Combine({"From", [Merged],"to",[Merged.1]}, " ")
Then ypu could remove other columns, only keep the last column
Best Regards
Maggie
This actually does not work...
Look at the October dates. It should be from October 29th to November 3rd
I think I need to add Month Name for week 2 also and then I can use both month names in the query. That should resolve it.
Ok all I needed to do was create a month name for Week 2, then Merge Month 2 and week 2 together which fixed it:
There is an issue with this.
Look at how the dates for October show:
There is no option to insert column.
Where do you add colum? I selected date but there is no insert option.
Ok got it---- Here is a picture in case anyone needs :
Thank you!
This is exactly what I was trying to do!
It's done pretty much the same way, usually with a Date table and WEEKNUM function
True, I do understanbd that, but here is what I get when using weekno function:
1
2
3
4
5
6
7
8
What I need is
Jan 1 - Jan 7
Jan 8 - Jan 14
Jan 15 - Jan 21
Jan 22 - Jan 28
Rather than the numbers.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
90 | |
86 | |
83 | |
64 | |
49 |
User | Count |
---|---|
127 | |
108 | |
88 | |
70 | |
66 |