Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I am facing the following issue:
I am trying to calculate the calendar weeks with WEEKNUM() and have the following errors with the two different variants of the function:
-Calendarweek=CONCATENATE(YEAR([date]), CONCATENATE(" - ", WEEKNUM([date])))
--> in this case it returns "2021-3" for the current week (starting on the 11th of January 2021).
--> This is not correct, as it should return "2021-2".
--> Then I tried to mitigate this and built in the "21" in the following function:
-Calendarweek=CONCATENATE(YEAR([date]), CONCATENATE(" - ", WEEKNUM([date],21)))
--> in this case it returns "2021-53" for the 1st of January until the 3rd of January 2021
--> This is clearly not correct!
How should I solve this problem?
Thank you in advance.
Solved! Go to Solution.
@Anonymous - I found a formula for ISO Year from a previous post. This should get you what you want.
CalendarWeek =
VAR __Wk =
WEEKNUM ( DateTab[Date], 21 )
VAR __ISOYr =
IF (
AND ( WEEKNUM ( DateTab[Date], 21 ) < 5, WEEKNUM ( DateTab[Date], 2 ) > 50 ),
DateTab[Year] + 1,
IF (
AND ( WEEKNUM ( DateTab[Date], 21 ) > 50, WEEKNUM ( DateTab[Date], 2 ) < 5 ),
DateTab[Year] - 1,
DateTab[Year]
)
)
RETURN
__ISOYr & "-" & __Wk
Hope this helps
David
@Anonymous - what do you expect the value for WEEKNUM for 1 Jan thru 3 Jan to be?
Hi! I'd like to have the 1st, 2nd and 3rd of January 31 still part of 2020 - 53.
How can I build this in?
@Anonymous - I found a formula for ISO Year from a previous post. This should get you what you want.
CalendarWeek =
VAR __Wk =
WEEKNUM ( DateTab[Date], 21 )
VAR __ISOYr =
IF (
AND ( WEEKNUM ( DateTab[Date], 21 ) < 5, WEEKNUM ( DateTab[Date], 2 ) > 50 ),
DateTab[Year] + 1,
IF (
AND ( WEEKNUM ( DateTab[Date], 21 ) > 50, WEEKNUM ( DateTab[Date], 2 ) < 5 ),
DateTab[Year] - 1,
DateTab[Year]
)
)
RETURN
__ISOYr & "-" & __Wk
Hope this helps
David
Fantastic! It worked!! Thank you. 🙂
Hi @Anonymous -
Using WEEKNUM without a second value (as in your first attempt) defaults to Sunday as the first day of the week.
Try WEEKNUM([date], 2) for weeks beginning on a Monday.
There is some explanation behind the use of 21 at this link: https://dax.guide/weeknum/
Hope this helps
David
Hi David,
Thank you for replying and your help!
Unfortunately it still returns 2021-3 for the current week.
How should I proceed?
Thank you in advance!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
19 | |
18 | |
16 | |
15 |
User | Count |
---|---|
51 | |
26 | |
22 | |
17 | |
16 |