How to Calculate Business Days in Power BI

Calculating business days in Power BI is essential for any report that measures performance over time, but it’s not as simple as subtracting two dates. To get an accurate picture of things like project durations or sales cycles, you need to exclude weekends and holidays. This tutorial will walk you through exactly how to create your own business day calculations using DAX functions, giving you reliable and context-aware business reporting.

Why Bother with Business Days?

Standard date calculations treat every day the same. For many business metrics, this just isn’t accurate. If a support ticket is opened at 5 PM on a Friday and resolved at 9 AM on Monday, it was open for three calendar days but only one business day. Reporting "3 days to resolution" misrepresents team performance and sets incorrect expectations.

Here are a few common scenarios where calculating business days is critical:

• Project Management: Measuring actual working days spent on a task for lead time and delivery estimates.
• Sales Analytics: Calculating the length of a sales cycle, from lead creation to deal won, in working days.
• Customer Support: Tracking ticket resolution times based on a standard Monday-to-Friday workweek.
• Operations & Logistics: Reporting on order-to-shipping times, excluding non-working days.

By moving from calendar days to business days, your reports become a far more accurate reflection of your team's efficiency and your business's operations.

The Building Blocks: Calculating Business Days with DAX

Power BI doesn’t have a built-in NETWORKDAYS function like Excel, so we have to build the logic ourselves using DAX (Data Analysis Expressions). The core concept is straightforward: start with the total number of days between two dates, then subtract all the non-working days.

To do this, we need to accomplish two things:

1. Exclude Weekends: We need to identify any Saturdays and Sundays that fall between our start and end dates.
2. Exclude Holidays: We need a separate list of company or public holidays to subtract from our calculation.

We'll accomplish this by creating a new calculated column in our data table. For this example, let's assume we have a simple table named 'Projects' with two date columns: [StartDate] and [EndDate].

Method 1: The Simple Way (Excluding Weekends Only)

First, let's tackle the most common requirement: calculating the number of weekdays between two dates. This method creates a dynamic list of dates for each row and counts only the ones that aren't a weekend.

Step 1: Understand the Key DAX Functions

We'll rely on a few essential DAX functions to build our formula:

• CALENDAR: Generates a single-column table of continuous dates between a specified start and end date. This is perfect for creating the date range we need to check.
• FILTER: Scans a table and returns only the rows that meet a condition we set. We'll use this to filter out the weekends.
• WEEKDAY: Returns a number representing the day of the week for a given date. By default, it uses 1 for Sunday and 7 for Saturday.
• COUNTROWS: Simply counts the number of rows in a table. We'll use this to count the final number of business days.

Step 2: Create the Calculated Column in Power BI

In your Power BI report, navigate to your data table (our 'Projects' table) and create a new calculated column. Here is the DAX formula to paste in:

Business Days (No Holidays) = VAR StartDate = 'Projects'[StartDate] VAR EndDate = 'Projects'[EndDate]

RETURN // Count the number of rows in a generated calendar // where the weekday is not a Saturday (7) or Sunday (1) COUNTROWS( FILTER( CALENDAR(StartDate, EndDate), WEEKDAY([Date], 1) <> 1 && WEEKDAY([Date], 1) <> 7 ) )

How This Formula Works

Let's break down what's happening step-by-step:

1. We declare two variables, StartDate and EndDate, to make the formula cleaner and easier to read. These grab the dates from the current row.
2. The CALENDAR(StartDate, EndDate) function generates a temporary, in-memory table with a single column named "[Date]," containing every single day between the start and end dates for that row.
3. The FILTER function iterates over this temporary table. For each date, WEEKDAY([Date], 1) checks what day it is.
4. The condition checks if the day is not equal to 1 (Sunday) and not equal to 7 (Saturday).
5. FILTER returns a new table containing only the dates that meet this condition (i.e., only the weekdays).
6. Finally, COUNTROWS counts the rows in that filtered table, giving us the total number of business days.

This formula is intuitive and effective for most datasets. You now have a column that correctly calculates working days, excluding weekends.

Method 2: The Complete Solution (Excluding Weekends AND Holidays)

Excluding weekends is a great start, but for full accuracy, you need to account for public and company holidays. To do this, we’ll expand on our previous formula and incorporate a simple Holiday table.

Step 1: Create a Holiday Table

The best way to manage holidays is with a dedicated table. You can create this easily in Power BI using the "Enter Data" feature or by importing it from an Excel spreadsheet or CSV.

Your table needs at least one column of dates. A second column with the holiday name is helpful for context. It should look something like this:

Let's name this table 'Holidays'. A key benefit of an external table is that it's easy to update each year without having to touch your complex DAX formulas.

Step 2: Update the DAX Formula

Now, let’s modify our original formula to check against this new 'Holidays' table. The logic is simple: first, count all the weekdays, then count how many applicable holidays fall in that period and subtract them.

Business Days (Full) =  
VAR StartDate = 'Projects'[StartDate]  
VAR EndDate = 'Projects'[EndDate]  
  
// 1. Calculate the total weekdays just like before  
VAR TotalWeekdays =  
    COUNTROWS(  
        FILTER(  
            CALENDAR(StartDate, EndDate),  
            WEEKDAY([Date], 1) <> 1 && WEEKDAY([Date], 1) <> 7  
        )  
    )  
  
// 2. Count holidays that fall in the period and ARE NOT on a weekend  
VAR CountOfHolidays =   
    COUNTROWS(  
        FILTER(  
            'Holidays',  
            'Holidays'[HolidayDate] >= StartDate && 'Holidays'[HolidayDate] <= EndDate &&  
            WEEKDAY('Holidays'[HolidayDate], 1) <> 1 && WEEKDAY('Holidays'[HolidayDate], 1) <> 7  
        )  
    )  
  
// 3. Subtract the holiday count from the weekday count  
RETURN  
    TotalWeekdays - CountOfHolidays

How This Improved Formula Works

This formula introduces an extra step:

1. The TotalWeekdays variable is identical to our original formula and calculates the number of business days, excluding weekends.
2. The CountOfHolidays variable scans your entire 'Holidays' table.
3. The FILTER function finds holidays where the [HolidayDate] falls inclusively between the [StartDate] and [EndDate] of the current row.
4. It also checks that the holiday does not fall on a Sunday or Saturday. This prevents double-subtracting holidays that land on weekends.
5. Finally, the RETURN statement subtracts the holiday count from the weekday count, giving you a truly accurate measure of business days.

Tips for Success and Common Pitfalls

Here are a few additional tips to get the most out of your business day calculations:

• Use a Proper Date Table: While these formulas are self-contained, Power BI best practices involve using a central Date or Calendar table for all time intelligence. Adding a "IsBusinessDay" flag (1 or 0) to your main Date table can be more performant on very large datasets than a row-by-row CALENDAR generation.
• Handle Regional Holidays: If your company operates across different countries or regions, add a 'Region' column to your 'Holidays' table. You can then add logic to your DAX to filter the holiday list based on the region of a specific project or sales office.
• Inclusive vs. Exclusive Dates: These formulas include both the start and end dates in the calculation. Some business processes might require excluding the first day (e.g., turnaround time). If so, you can adjust the logic slightly by using StartDate + 1.

Final Thoughts

You’ve now learned how to create robust, customized business day calculations in Power BI. By building custom DAX calculated columns that account for weekends and specific holidays, you can transform your raw data into metrics that accurately reflect how your business actually operates.

Mastering these DAX patterns is rewarding, but it can also be time-consuming. Instead of manually writing calculated columns and managing date logic, we built Graphed to do the heavy lifting for you. You can connect your data sources in minutes and simply ask questions in plain English, like "show me the average sales cycle in business days per rep this quarter." We designed the platform to handle these complex calculations automatically, delivering instant dashboards and insights without you ever needing to open the DAX editor.