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

     

     

     

     

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Probably there is a better way, but i would do:

 

 

where 

 

Weekday = WEEKDAY(Tabla[Date].[Date],2)
 
Weeknum = calculate(count(Tabla[Weekday]),FILTER(Tabla,Tabla[Weekday]=4),FILTER(Tabla,Tabla[Date].[Date]<=EARLIER(Tabla[Date].[Date])))+1
 
Ïn weeknum formula the "=4" it's because your week starts on thursday
  • Vijay08V's avatar
    Vijay08V
    Helper III
    3 years ago

    Not why sure but this logic was not working for me. I was getting Week nums as attached snap