Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi Community
I really need your help on how to create a calendar that automatically knows that when it comes to the beginning of 2021, this is still week 53 and not week 1. I have only managed to create a calendar where the week number starts by 1 when entering a new calendar year. But for year 2021: the 1st to the 3rd january is week 53 and then week 1 starting from january 4th.
@PowerQuery @calendar@weeknum
How do i build in this knowledge ??
I rellay hope that someone has the answer to this, t
Solved! Go to Solution.
Hi @micjensen ,
This is very complex logic to code into Power Query.
I would recommend using WEEKNUM([Date], 21) in a DAX calculated column to be honest:
Pete
Proud to be a Datanaut!
Please see this article with a good way to handle that.
445 Calendar with 53-Week Years – Hoosier BI
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Here's a custom function in M code for calculating the ISO Weeknumber (if that is what you want):
//fxISOWeekNum
(theDate as date) =>
let
a = Date.AddDays(theDate,-1),
b = Date.DayOfWeek(a,Day.Sunday),
c = Date.AddDays(theDate,-b + 3),
d2 = #date(Date.Year(c),1,3),
IWN = Number.IntegerDivide(Number.From(theDate)-Number.From(d2) + Date.DayOfWeek(d2)+6,7)
in
IWN
Please look Function for ISO Year/Week number (ISO 8601))
NB! Pay attention to offsetindays parameter (by default it equals to zero - it means week starts on Sunday)
Here's a custom function in M code for calculating the ISO Weeknumber (if that is what you want):
//fxISOWeekNum
(theDate as date) =>
let
a = Date.AddDays(theDate,-1),
b = Date.DayOfWeek(a,Day.Sunday),
c = Date.AddDays(theDate,-b + 3),
d2 = #date(Date.Year(c),1,3),
IWN = Number.IntegerDivide(Number.From(theDate)-Number.From(d2) + Date.DayOfWeek(d2)+6,7)
in
IWN
Please see this article with a good way to handle that.
445 Calendar with 53-Week Years – Hoosier BI
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi @micjensen ,
This is very complex logic to code into Power Query.
I would recommend using WEEKNUM([Date], 21) in a DAX calculated column to be honest:
Pete
Proud to be a Datanaut!