Forum Discussion

Vijay08V's avatar
Vijay08V
Helper III
3 years ago
Solved

Need help in DAX - Custom Week Num

Hi,   I am trying to write DAX function to get 52 rolling weeks in a year assuming my week starts every Thursday and ends on Wednesday. Need some help on the logics   M/D/Y WK DAY 1-01-20...
Need Help
  • Ahmedx's avatar
    Ahmedx
    3 years ago

    You need to create a custom calendar

     

     

    Date = ADDCOLUMNS(CALENDAR(DATE(2020,1,1),DATE(2023,12,31)),
      "Week Day",WEEKDAY([Date],14), //start of the week is thursday
      "Weeknum" ,WEEKNUM([Date],14),
      "Year" , Year([Date])
      )

     

     

    and only then count the sliding weeks of the year,

     

     

    rolling weeks = 
VAR _CurrentWeek =
    MAX('Date'[Weeknum])
RETURN
    CALCULATE([total],'Date'[Weeknum]<=_CurrentWeek)

     

     

    you can download the file here
    https://dropmefiles.com/DJb1i

     

     

     

     

Ahmedx's avatar
Ahmedx
Super User
3 years ago

You need to create a custom calendar

 

 

Date = ADDCOLUMNS(CALENDAR(DATE(2020,1,1),DATE(2023,12,31)),
      "Week Day",WEEKDAY([Date],14), //start of the week is thursday
      "Weeknum" ,WEEKNUM([Date],14),
      "Year" , Year([Date])
      )

 

 

and only then count the sliding weeks of the year,

 

 

rolling weeks = 
VAR _CurrentWeek =
    MAX('Date'[Weeknum])
RETURN
    CALCULATE([total],'Date'[Weeknum]<=_CurrentWeek)

 

 

you can download the file here
https://dropmefiles.com/DJb1i