Customize Week Number (Week starting from Saturday)

[mukeshved80]

Dears,

Kindly note, I am a new user of Power BI. Learing through online videos.

I need help related to week number.

For my report, week starts on Saturday.

I was trying using "weeknum" function but I am unable to get desired result.

I need to display "Week Number" and accordingly week start and end dates on BI report.

Kindly advise.

Thanks & Regards.

[rajendraongole1 · Accepted solution]

Hi @mukeshved80  - Hope you have a date table created at your end, if Not, 

DateTable1 =

ADDCOLUMNS (

    CALENDAR ( DATE ( 2022, 1, 1 ), DATE ( 2024, 12, 31 ) ),-- dates considering from 2022 to 2024

    "Year", YEAR ( [Date] ),

    "Month", MONTH ( [Date] ),

    "Day", DAY ( [Date] ),

    "Weekday", WEEKDAY ( [Date], 2 ),  -- Returns 1 for Monday and 7 for Sunday

    "WeekStart", [Date] - WEEKDAY ( [Date], 2 ) + 6  -- Calculate the previous Saturday

)

Add a calculated column in your date table to determine the week number starting from Saturday.

WeekNumber =
VAR CurrentDate = [Date]
VAR PreviousSaturday = CurrentDate - WEEKDAY(CurrentDate, 1) + 7
RETURN
WEEKNUM(PreviousSaturday, 1)

another new column 

WeekStartDate =
[Date] - WEEKDAY([Date], 1) + 7

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[Anonymous · Accepted solution]

Hi @mukeshved80 

Thanks for the reply from @rajendraongole1 . Here I have another idea in mind, and I would like to share it for reference.

My sample:

Create several calculated columns as follow

WeekNum = WEEKNUM([Date], 16)

Week Start Date = [Date] - WEEKDAY([Date], 3) - 2

Week End Date = [Week Start Date] + 6

Output:

For a detailed description of the formulas used, see the following documentation:

WEEKNUM function (DAX) - DAX | Microsoft Learn

WEEKDAY function (DAX) - DAX | Microsoft Learn

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rajendraongole1 · Accepted solution]

Hi @mukeshved80  - Hope you have a date table created at your end, if Not, 

DateTable1 =

ADDCOLUMNS (

    CALENDAR ( DATE ( 2022, 1, 1 ), DATE ( 2024, 12, 31 ) ),-- dates considering from 2022 to 2024

    "Year", YEAR ( [Date] ),

    "Month", MONTH ( [Date] ),

    "Day", DAY ( [Date] ),

    "Weekday", WEEKDAY ( [Date], 2 ),  -- Returns 1 for Monday and 7 for Sunday

    "WeekStart", [Date] - WEEKDAY ( [Date], 2 ) + 6  -- Calculate the previous Saturday

)

Add a calculated column in your date table to determine the week number starting from Saturday.

WeekNumber =
VAR CurrentDate = [Date]
VAR PreviousSaturday = CurrentDate - WEEKDAY(CurrentDate, 1) + 7
RETURN
WEEKNUM(PreviousSaturday, 1)

another new column 

WeekStartDate =
[Date] - WEEKDAY([Date], 1) + 7

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!