Search code examples
powerbidaxm

Power BI: Calculating Week number from Date Column, starting from Saturday and Ending on Friday

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?

Solution

  • 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).