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/YWKDAY
1-01-2019Week 1Tuesday
1-02-2019Week 1Wednesday
1-03-2019Week 2Thursday
1-04-2019Week 2Friday
1-05-2019Week 2Saturday
1-06-2019Week 2Sunday
1-07-2019Week 2Monday
1-08-2019Week 2Tuesday
1-09-2019Week 2Wednesday
1-10-2019Week 3Thursday
1-11-2019Week 3Friday
1-12-2019Week 3Saturday
1-13-2019Week 3Sunday
1-14-2019Week 3Monday
1-15-2019Week 3Tuesday
1-16-2019Week 3Wednesday
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

     

     

     

     

4 Replies

  • 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

  • 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

     

     

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Do you have more than one year in the table?