Forum Discussion

mehul_sharma77's avatar
mehul_sharma77
Regular Visitor
1 year ago

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

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!

Tips and Tricks

4 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      rajendraongole1
      Icon for Super User rankSuper User
      1 year ago

      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())
  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Icon for Super User rankSuper User
    1 year ago

    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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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