Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
mehul_sharma77
Regular Visitor

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!

4 REPLIES 4
Anonymous
Not applicable

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:

vyangliumsft_0-1731913120013.png

 

 

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
Super User
Super User

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:

Bibiano_Geraldo_0-1731695510712.png



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
Super User
Super User

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_0-1731695101807.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





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

 

rajendraongole1_0-1731936011154.png

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors