2

I need to calculate WeekOfYear from date column, which starts from Sat and ends on next Friday. I tried WEEKNUM DAX Function, but the option for WeekStart Day is limited which is 1 or 2 (Sunday or Monday),

How can calculate the week numbers which starts from the Saturdays?

Ashish25
  • 1,965
  • 1
  • 15
  • 16

1 Answers1

1

The shortest way to do this is probably to use the WEEKNUM function with an offset and adjustment:

WeekNum = WEEKNUM(DateTable[Date] + 1) +
              IF(WEEKDAY(DATE(YEAR(DateTable[Date]), 1, 1)) = 7, -1, 0)

For most years you just need WEEKNUM(DateTable[Date] + 1), but if the year starts on a Saturday (e.g. 2011), then this would start the year on Week 2 so we need to subtract off a week in those cases. The code WEEKDAY(DATE(YEAR(DateTable[Date]), 1, 1)) = 7 tests if January 1st of the year DateTable[Date] is a Saturday (7th day of the week).

Alexis Olson
  • 38,724
  • 7
  • 42
  • 64