How to calculate business days between dates in DAX: step by step

João Barros 12 de September de 2026 3 min read

Calculating business days between two dates in DAX is a common requirement in project, SLA and operations reports where weekends and holidays do not count toward the deadline. This guide explains the reasoning behind the logic (filtering the Date table, excluding weekdays and holidays) and provides concrete examples with minimal DAX code you can paste into Power BI Desktop. By following the steps you will obtain reusable, easy-to-test measures.

Prerequisites

Power BI Desktop or Analysis Services with DAX support.
A Date table with a Date column and Year, Month, Weekday, etc. columns. Ideally marked as Date table in Modelation.
An activities/events table with StartDate and EndDate columns.
A Holidays table (HolidayDate) with a Date column listing the holidays to exclude.

These tables allow using range filters and functions like FILTER, COUNTROWS, EXCEPT or RELATEDTABLE in DAX. For very large datasets (for example, >100k rows in Activities) consider measuring performance: SUMX per row works but can be slower than a solution using calculated columns or pre-aggregations.

Step 1: Prepare the Date table

It is essential to have a Date table marked as Date table in Power BI. The Date table makes it easy to create filters between StartDate and EndDate and to calculate columns like Weekday which we then use to exclude Saturday and Sunday. Example: a calendar from 2020 to 2026 covers 7 years — 2,557 days (approx.).

// Minimal example of creating a Date table in DAX (New table)
Date =
CALENDAR(DATE(2020,1,1), DATE(2026,12,31))

// Add weekday (1=Sunday..7=Saturday)
Date =
ADDCOLUMNS(
  CALENDAR(DATE(2020,1,1), DATE(2026,12,31)),
  "Year", YEAR([Date]),
  "Month", MONTH([Date]),
  "Weekday", WEEKDAY([Date],1) // 1 = domingo
)

Note about WEEKDAY: WEEKDAY([Date],1) returns 1=Sunday ... 7=Saturday. If you prefer to use 1=Monday..7=Sunday use WEEKDAY([Date],2) and then exclude {6,7} for Saturday/Sunday. Confirm the convention to avoid excluding the wrong days.

Step 2: Create the Holidays table

Create a simple table with the holiday dates. It can be imported from a CSV file, maintained manually or created in DAX for example purposes. For 2024 include fixed holidays and, if possible, movable ones (Easter) — this will prevent incorrect counts. Example with 3 holidays for testing:

// Example of holidays table in DAX (New table)
Holidays =
DATATABLE(
  "HolidayDate", DATE,
  {
    { DATE(2024,1,1) },
    { DATE(2024,4,25) },
    { DATE(2024,12,25) }
  }
)

For Portugal, April 25 and May 1 are typical; also include regional holidays if needed. Validate that the dates are within the Date table range.

Step 3: Relate tables

Create relationships between the Date table and the Holidays table (Date[Date] -> Holidays[HolidayDate]) and, if it makes sense, between the Date table and the Activities table (for example, using an active relationship on Date[Date] to enable date navigation). These relationships allow filter functions in DAX to act correctly in the model context.

Without proper relationships, functions like RELATEDTABLE or FILTER may not find the expected rows and measures will return incorrect values. Check the relationship direction (one-to-many) and that the Date column is of type date.

Step 4: Measure to count business days between two dates (example)

The measure below shows the idea: we filter the Date table by the range StartDate..EndDate, exclude weekends and subtract holidays using EXCEPT. The example is safe for ranges that can span multiple years.

Business Days =
VAR _Start = MIN(Activities[StartDate])
VAR _End = MAX(Activities[EndDate])

RETURN
CALCULATE(
  COUNTROWS('Date'),
  FILTER(
    'Date',
    'Date'[Date] >= _Start &&
    'Date'[Date] <= _End &&
    NOT('Date'[Weekday] IN {1,7}) && // 1=domingo,7=sábado se WEEKDAY(...,1)
    NOT( RELATEDTABLE(Holidays) ) // ver nota abaixo
  )
)

Technical note: RELATEDTABLE(Holidays) does not work as a direct boolean — so the more robust alternative is to use EXCEPT to remove the dates that appear in the Holidays table within the same range. The EXCEPT version appears right below in the same code file and is recommended.

Step 5: Alternative measure with SUMX (per-row case)

If you want to calculate business days per row in the Activities table (for example, sum of business days per project), use SUMX to iterate rows. This is useful when each row has distinct StartDate/EndDate and you want the aggregated total. Performance example: for 10,000 rows, SUMX may take seconds; consider optimizing if needed.

Business Days Per Activity =
SUMX(
  Activities,
  VAR _s = Activities[StartDate]
  VAR _e = Activities[EndDate]
  VAR _d =
    EXCEPT(
      SELECTCOLUMNS(FILTER('Date', 'Date'[Date] >= _s && 'Date'[Date] <= _e && NOT('Date'[Weekday] IN {1,7})), "D", 'Date'[Date]),
      SELECTCOLUMNS(FILTER(Holidays, Holidays[HolidayDate] >= _s && Holidays[HolidayDate] <= _e), "D", Holidays[HolidayDate])
    )
  RETURN COUNTROWS(_d)
)

Concrete example: for Activities with StartDate = 2024-04-22 and EndDate = 2024-04-30, total days = 9, weekend (27-28) = 2, holiday (25-04) within the range = 1 → Business Days = 9 - 2 - 1 = 6. Test cases such as same-day intervals, intervals that start and end on a weekend and intervals that include multiple consecutive holidays.

Verify the result

Create a table visual with Activities[StartDate], Activities[EndDate] and the Business Days or Business Days Per Activity measure. Manually verify a few records: count days between dates, subtract Saturdays/Sundays and confirm that dates in Holidays are excluded. Common errors: Date table not marked as Date table, Weekday misinterpreted (confirm base 1 or 2), holidays outside the range or missing relationship. To validate at scale, filter by a month and compare with an Excel calculation for 50 test rows.

Conclusion

With these measures you can calculate business days between two dates in DAX, excluding weekends and holidays. For production: keep the holidays table up to date (include movable holidays), document the WEEKDAY convention in the model and test edge cases. If you need better performance for thousands of rows, consider calculated columns instead of SUMX or pre-processing in the ETL layer. Final tip: create a control measure that returns the weekend days and holidays found per range to facilitate auditing.