Need help in DAX - Custom Week Num

[Vijay08V]

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

[Ahmedx · Accepted solution]

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]

Do you have more than one year in the table?

[Anonymous]

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]

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