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.
Hi all,
Below is a calender, which I use in my report.
However, the weeknumber is not according to ISO8601.
I know there is a possibility with the formula WEEKNUM: WEEKNUM([Date], 21)
the 21 gives me the weeknumber according to ISO8601.
But how must I change the tellow marked line below to get this done?
Hope someone can help me out with this!
John
Solved! Go to Solution.
I will take you on your word that weeknum (21) gives what you need. Weeknum is a DAX function, not a PQ function. So don't add the weeknum in PQ, but instead add it as a calculated column in DAX. I would normally not advise this, but based on your post, it would seem that it could work.
Dear Matt,
Highly appreciated, followed your advise, all fine now.
Have a good day!
John
I notice @MattAllington just replied as well while I was investigating this.
After lookup up the ISO 8601 definition, I see that an ISO week runs from Monday to Sunday, and the year a given week falls in is the year containing the Thursday of that week.
Here is a formula that should work in Power Query in the context of the code you posted:
= Table.AddColumn(#"Inserted Month", "Week of Year",
each let
DayOfWeek = Date.DayOfWeek([Date],Day.Monday),
ThursdayDate = Date.AddDays([Date],3-DayOfWeek),
ThursdayDayOfYear = Date.DayOfYear(ThursdayDate)
in
Number.IntegerDivide(6+ThursdayDayOfYear, 7 ),
Int64.Type
)
It basically says:
This works because, for example, if the Thursday of a given week falls within days 1 to 7 of the year, the division in step 3 ranges between 7 / 7 to 13 / 7, all of which return 1, which is correct since that must be the first Thursday of the year. Subsequent weeks will increment accordingly.
Regards,
Owen
I will take you on your word that weeknum (21) gives what you need. Weeknum is a DAX function, not a PQ function. So don't add the weeknum in PQ, but instead add it as a calculated column in DAX. I would normally not advise this, but based on your post, it would seem that it could work.
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |