How to Create a Date Table in DAX with Week Numbers (5-Day Weeks, Excluding Weekends)?

[mehul_sharma77]

Hello,

I need help creating a Date table in Power BI using DAX. The table should include columns for day week month and year formats. Additionally, I need to assign week numbers based on the following criteria:

1. Each week contains 5 days (Monday to Friday).
2. Saturday and Sunday are excluded from the week count.
3. If the month starts on a day other than Monday, the first week should include only the available weekdays (e.g., if the month starts on Thursday, Week 1 will have only Thursday and Friday).
4. The week numbers should restart with each month (i.e., Week 1  Week 2 Week 3  etc.).

Could someone provide a DAX formula or guidance to implement this logic in a calculated table?

Thanks in advance for your help!

[Anonymous]

Thanks for the reply from Bibiano_Geraldo  and rajendraongole1 , please allow me to provide another insight: 
Hi  @mehul_sharma77 ,

Here are the steps you can follow：

1. Create calculated table.

Table_flag =
var _table=
CALENDAR(
    DATE(2023,1,1),
    DATE(2024,12,31))
var _addtable=
ADDCOLUMNS(    _table,"Year",YEAR([Date]),"Month",MONTH([Date]),"Week_Test",WEEKNUM([Date],2),"day",WEEKDAY([Date],2))
var _step1_3=
FILTER(
    _addtable,[day]>=1&&[day]<=5)
var _weektable=
ADDCOLUMNS(
    _step1_3,"week",RANKX(FILTER(_step1_3,[Year]=EARLIER([Year])&&[Month]=EARLIER([Month])),[Week_Test],,ASC,Dense))
return
SUMMARIZE(
    _weektable,
    [Date],[Year],[Month],[week],[day])

2. Result:

Best Regards,

Liu Yang

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

[Bibiano_Geraldo]

Hi, @mehul_sharma77 ,
First of all, create a new calculated Table and paste this DAX:

DateTable = 
ADDCOLUMNS(
    FILTER(
        CALENDAR(DATE(2023, 1, 1), DATE(2023, 12, 31)), -- Adjust start and end dates
        WEEKDAY([Date], 2) <= 5 -- Exclude Saturdays (6) and Sundays (7)
    ),
    "Day Name", FORMAT([Date], "dddd")
)

We can now add the week numbers without worrying about weekends since they are excluded entirely by this DAX:

Week Number = 
VAR CurrentMonth = MONTH([Date])
VAR FirstDateOfMonth =
    CALCULATE(
        MIN([Date]),
        FILTER(
            ALL(DateTable),
            MONTH([Date]) = CurrentMonth
        )
    )
VAR DaysSinceFirstDate =
    DATEDIFF(FirstDateOfMonth, [Date], DAY)
VAR WeekNum = INT(DaysSinceFirstDate / 5) + 1
RETURN WeekNum

 
At this point, your table should look like this, as you can see in the filter, sunday's and saturday's are not included:

I hope this helps! 🎯
If you found this answer helpful:
✔️ Mark it as the solution to help others find it faster.
👍 Give it a kudo to show your appreciation!

[rajendraongole1]

Hi @mehul_sharma77 - you can following DAX formula to create a Date table. It includes the logic for your custom week numbering and day/week/month/year formats.

use calculated table:

DateTable =

VAR StartDate = DATE(2024, 1, 1)  -- Replace with your desired start date

VAR EndDate = DATE(2024, 12, 31)  -- Replace with your desired end date

VAR DateRange = ADDCOLUMNS(

    CALENDAR(StartDate, EndDate),

    "Year", YEAR([Date]),

    "Month", MONTH([Date]),

    "Month Name", FORMAT([Date], "MMMM"),

    "Day", DAY([Date]),

    "Day Name", FORMAT([Date], "dddd"),

    "IsWeekday", IF(WEEKDAY([Date], 2) <= 5, 1, 0),  -- 1 if Monday-Friday

    "WeekdayNumber", WEEKDAY([Date], 2)  -- Monday=1, ..., Sunday=7

)

VAR AddWeekNumber =

    ADDCOLUMNS(

        DateRange,

        "Week In Month",

        VAR CurrentMonth = MONTH([Date])

        VAR CurrentYear = YEAR([Date])

        VAR FirstDayOfMonth = DATE(CurrentYear, CurrentMonth, 1)

        VAR FirstMonday =

            IF(WEEKDAY(FirstDayOfMonth, 2) = 1,

               FirstDayOfMonth,

               FirstDayOfMonth + (8 - WEEKDAY(FirstDayOfMonth, 2)))

        VAR DaysSinceFirstMonday = DATEDIFF(FirstMonday, [Date], DAY)

        RETURN

            IF([IsWeekday] = 1, INT(DaysSinceFirstMonday / 5) + 1, BLANK())

    )

RETURN

    FILTER(AddWeekNumber, [Week In Month] <> BLANK())

[rajendraongole1]

Hi @mehul_sharma77 - I hope it any of the above solutions works, please check and share your views

DateTable_t =

VAR StartDate = DATE(2024, 1, 1)  -- Replace with your desired start date

VAR EndDate = DATE(2024, 12, 31)  -- Replace with your desired end date

VAR DateRange = ADDCOLUMNS(

    CALENDAR(StartDate, EndDate),

    "Year", YEAR([Date]),

    "Month", MONTH([Date]),

    "Month Name", FORMAT([Date], "MMMM"),

    "Day", DAY([Date]),

    "Day Name", FORMAT([Date], "dddd"),

    "IsWeekday", IF(WEEKDAY([Date], 2) <= 5, 1, 0),  -- 1 if Monday-Friday

    "WeekdayNumber", WEEKDAY([Date], 2)  -- Monday=1, ..., Sunday=7

)

VAR AddWeekNumber =

    ADDCOLUMNS(

        DateRange,

        "Week In Month",

        VAR CurrentMonth = MONTH([Date])

        VAR CurrentYear = YEAR([Date])

        VAR FirstDayOfMonth = DATE(CurrentYear, CurrentMonth, 1)

        VAR FirstMonday =

            IF(WEEKDAY(FirstDayOfMonth, 2) = 1,

               FirstDayOfMonth,

               FirstDayOfMonth + (8 - WEEKDAY(FirstDayOfMonth, 2)))

        VAR DaysSinceFirstMonday = DATEDIFF(FirstMonday, [Date], DAY)

        RETURN

            IF([IsWeekday] = 1, INT(DaysSinceFirstMonday / 5) + 1, BLANK())

    )

RETURN

    FILTER(AddWeekNumber, [Week In Month] <> BLANK())