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
I have the following issue - I know how to convert dates to ISO 8601 Week of Year numbers (in order for the year numbering to begin with the week containing the first Thursday of the year) with = WEEKNUM('Table'[Date Only], 21)
This works just fine with my data, however, we'd still like to use the U.S. way of Day of Week numbering with SUNDAY beginning the weeks rather than Monday. Is this possible?
Here is an example of what I mean visually:
ISO 8601 Week 53 (2019) and Week 1 (2021) with Mondays as the first day of the week (the original way)
|
| Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday |
Week 53 |
| 12/28/2020 | 12/29/2020 | 12/30/2020 | 12/31/2020 | 1/1/2021 | 1/2/2021 | 1/3/2021 |
Week 1 |
| 1/4/2021 | 1/5/2021 | 1/6/2021 | 1/7/2021 | 1/8/2021 | 1/9/2021 | 1/10/2021 |
ISO 8601 Week 53 (2019) and Week 1 (2021) with Sundays as the first day of the week (the way we'd like it)
|
| Sunday | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday |
Week 53 |
| 12/27/2020 | 12/28/2020 | 12/29/2020 | 12/30/2020 | 12/31/2020 | 1/1/2021 | 1/2/2021 |
Week 1 |
| 1/3/2021 | 1/4/2021 | 1/5/2021 | 1/6/2021 | 1/7/2021 | 1/8/2021 | 1/9/2021 |
Thank you in advance for any assistance!
Solved! Go to Solution.
Hi @Anonymous
Try to build a new weeknum column and year by dax code.
Date =
ADDCOLUMNS (
CALENDAR ( DATE ( 2019, 01, 01 ), DATE ( 2021, 12, 31 ) ),
"Year", YEAR ( [Date] ),
"Month", MONTH ( [Date] ),
"WeekNum", WEEKNUM ( [Date], 1 ),
"WeekDay", WEEKDAY ( [Date], 1 ),
"MonthName", FORMAT ( [Date], "MMMM" ),
"DayName", FORMAT ( [Date], "DDDD" )
)
Here I have a date table from 2019 to 2021, if we use weeknum function directly we will get weeknum =1 in the begain of a year. Try this code.
ISO 8601 WeekNum =
VAR _COUNT0 =
CALCULATE (
COUNTROWS ( 'Date' ),
FILTER (
'Date',
'Date'[Year] = EARLIER ( 'Date'[Year] )
&& 'Date'[WeekNum] - 1 = 0
)
)
VAR _BASENUM1 =
IF ( _COUNT0 < 7, 'Date'[WeekNum] - 1, 'Date'[WeekNum] )
VAR _ISO_8601_WeekNum =
IF (
WEEKDAY ( DATE ( 'Date'[Year] - 1, 01, 01 ) ) <> 1
&& 'Date'[Year] = 'Date'[Year]
&& _BASENUM1 = 0,
WEEKNUM ( DATE ( MIN ( 'Date'[Year] ), 12, 31 ), 1 ) - 1,
_BASENUM1
)
RETURN
_ISO_8601_WeekNum
Text Week =
COMBINEVALUES(" ","Week",'Date'[ISO 8601 WeekNum])
WeekYear =
VAR _COUNT0 =
CALCULATE (
COUNTROWS ( 'Date' ),
FILTER (
'Date',
'Date'[Year] = EARLIER ( 'Date'[Year] )
&& 'Date'[WeekNum] - 1 = 0
)
)
VAR _BASENUM1 =
IF ( _COUNT0 < 7, 'Date'[WeekNum] - 1, 'Date'[WeekNum] )
RETURN
IF(_BASENUM1 = 0,'Date'[Year] -1,'Date'[Year])
Result is as below. I only show week1 and week52 in my matrix.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous if you don't give the 2nd parameter to WEEKNUM function it will start from Sunday, not sure if that is what you are looking for.
✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Unfortunately, it's not. I want to use the European way of week numbering (ISO 8601) that doesn't start Week 1 of the year with January 1st.
Hi @Anonymous
Try to build a new weeknum column and year by dax code.
Date =
ADDCOLUMNS (
CALENDAR ( DATE ( 2019, 01, 01 ), DATE ( 2021, 12, 31 ) ),
"Year", YEAR ( [Date] ),
"Month", MONTH ( [Date] ),
"WeekNum", WEEKNUM ( [Date], 1 ),
"WeekDay", WEEKDAY ( [Date], 1 ),
"MonthName", FORMAT ( [Date], "MMMM" ),
"DayName", FORMAT ( [Date], "DDDD" )
)
Here I have a date table from 2019 to 2021, if we use weeknum function directly we will get weeknum =1 in the begain of a year. Try this code.
ISO 8601 WeekNum =
VAR _COUNT0 =
CALCULATE (
COUNTROWS ( 'Date' ),
FILTER (
'Date',
'Date'[Year] = EARLIER ( 'Date'[Year] )
&& 'Date'[WeekNum] - 1 = 0
)
)
VAR _BASENUM1 =
IF ( _COUNT0 < 7, 'Date'[WeekNum] - 1, 'Date'[WeekNum] )
VAR _ISO_8601_WeekNum =
IF (
WEEKDAY ( DATE ( 'Date'[Year] - 1, 01, 01 ) ) <> 1
&& 'Date'[Year] = 'Date'[Year]
&& _BASENUM1 = 0,
WEEKNUM ( DATE ( MIN ( 'Date'[Year] ), 12, 31 ), 1 ) - 1,
_BASENUM1
)
RETURN
_ISO_8601_WeekNum
Text Week =
COMBINEVALUES(" ","Week",'Date'[ISO 8601 WeekNum])
WeekYear =
VAR _COUNT0 =
CALCULATE (
COUNTROWS ( 'Date' ),
FILTER (
'Date',
'Date'[Year] = EARLIER ( 'Date'[Year] )
&& 'Date'[WeekNum] - 1 = 0
)
)
VAR _BASENUM1 =
IF ( _COUNT0 < 7, 'Date'[WeekNum] - 1, 'Date'[WeekNum] )
RETURN
IF(_BASENUM1 = 0,'Date'[Year] -1,'Date'[Year])
Result is as below. I only show week1 and week52 in my matrix.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Check 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 |
---|---|
115 | |
113 | |
105 | |
95 | |
58 |
User | Count |
---|---|
175 | |
147 | |
134 | |
105 | |
82 |