Forum Discussion

micjensen's avatar
micjensen
Frequent Visitor
4 years ago
Solved

Power Query Calendar Creation - 53 weeks

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 ...
Need Help
Tips and Tricks
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    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

  • ronrsnfld's avatar
    ronrsnfld
    4 years ago

    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