CODEWITHSUNDEEP
powerbidaxm
Ashish25
Ashish25

Reputation: 2023

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?

Upvotes: 2

Views: 11861

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40264

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

Upvotes: 1

Related Questions