How to Calculate Working Days in Power BI

Calculating the true duration of a project or the time an invoice has been outstanding requires you to think in business terms, not calendar days. The key is to count only the working days, excluding weekends and public holidays. This article will show you how to do just that in Power BI using DAX, so you can build more accurate and meaningful reports.

Why Calculating Working Days Matters

Simply subtracting a start date from an end date gives you the total calendar days, which is often misleading. A task that takes 7 calendar days might only involve 5 working days, but if it spans over a major holiday, it could be just 4. For business reporting, this distinction is critical.

Here are a few scenarios where counting working days is essential:

• Project Management: Accurately calculate task durations and project timelines.
• Sales Operations: Measure the true length of your sales cycle, from lead creation to closing a deal.
• Customer Support: Track ticket resolution times against SLAs (Service Level Agreements) that are based on business days.
• Finance: Report on days sales outstanding (DSO) or invoice payment times, ignoring non-business days.

By moving from calendar days to working days, your metrics become much more representative of your team's actual effort and operational cadence.

Start with a Solid Foundation: The Date Table

Before writing any duration formulas, the most important best practice in Power BI is to set up a dedicated date table, also known as a calendar table. This table acts as a central source for all things date-related in your report and is essential for time-intelligence functions to work correctly.

Your date table should contain a row for every single day within the range of your data (e.g., from January 1, 2020, to December 31, 2025) and include columns for year, quarter, month, day name, and day of the week number.

If you don't have one, you can create a simple one in Power BI using DAX. Navigate to the "Table tools" tab and click "New table." Then paste in this formula, adjusting the start and end dates to fit your needs:

Date Table = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2022, 1, 1 ), DATE ( 2025, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month Name", FORMAT ( [Date], "mmmm" ),
    "Month Number", MONTH ( [Date] ),
    "Day of Week", WEEKDAY ( [Date], 2)   // Monday=1, Sunday=7. US use 1 as the second argument instead
)

After creating it, be sure to right-click the table in the Fields pane and select Mark as date table, identifying the main 'Date' column as the unique identifier. This tells Power BI how to handle time-based calculations for your entire model.

Introducing the NETWORKDAYS DAX Function

The star of the show for calculating working days in Power BI is the NETWORKDAYS function. It’s a powerful DAX function that does exactly what its name implies: it calculates the number of whole working days between two dates.

The syntax looks like this:

NETWORKDAYS(<start_date>, <end_date>, [<weekend>], [<holidays>])

Breaking Down the Parameters:

• <start_date> and <end_date>: These are the two dates between which you want to count the working days. They typically come from columns in your data, like 'OrderDate' and 'ShipDate'.
• [<weekend>] (Optional): This is a number that specifies which days of the week are considered weekends. If you omit it, it defaults to 1, which means Saturday and Sunday. We'll explore other options later.
• [<holidays>] (Optional): This parameter allows you to specify a list of public holidays that should not be counted as working days.

A Simple Example

Imagine you have a 'Projects' table with 'StartDate' and 'EndDate' columns. To add a new column that calculates the working days for each project, you would select "New column" and use this formula:

Project Working Days = NETWORKDAYS(Projects[StartDate], Projects[EndDate])

This formula is a great starting point. It assumes a standard Saturday/Sunday weekend and doesn't account for any public holidays. To achieve true accuracy, we need to incorporate holidays.

Factoring in Holidays for Realistic Calculations

Excluding only weekends is rarely enough. Public holidays like New Year's Day, Memorial Day, and Christmas can significantly impact your timeline calculations. The NETWORKDAYS function makes this easy by letting you provide a list of holiday dates to exclude.

The best way to manage this is to create a separate 'Holidays' table in your Power BI model. You can do this by importing a simple two-column spreadsheet (e.g., 'Holiday Name', 'Date') or by creating one directly in Power BI with DAX:

1. Go to the "Home" ribbon and click "Enter data."
2. Name your first column "Holiday" and the second "Date." Set their data types to Text and Date, respectively.
3. Enter your company's list of paid holidays.

Alternatively, you can create the table with DAX like this:

Holidays = 
DATATABLE (
    "Holiday", STRING,
    "Date", DATETIME,
    {
        { "New Year's Day", "2024-01-01" },
        { "Memorial Day", "2024-05-27" },
        { "Independence Day", "2024-07-04" },
        { "Labor Day", "2024-09-02" },
        { "Thanksgiving Day", "2024-11-28" },
        { "Christmas Day", "2024-12-25" }
    }
)

Now, you can reference the date column from this new 'Holidays' table in your NETWORKDAYS function:

Working Days excl. Holidays = NETWORKDAYS(Projects[StartDate], Projects[EndDate], 1, Holidays[Date])

Power BI will now count the days between the start and end dates, skipping both weekends (Saturday and Sunday, because we used '1') and any date that appears in your 'Holidays' table.

Handling Non-Standard Work Weeks

Not every business operates Monday to Friday. Retailers might have their "weekend" on a Tuesday and Wednesday, while manufacturing plants might operate six days a week. The weekend parameter in NETWORKDAYS gives you a lot of flexibility here.

This parameter accepts a number that corresponds to a specific weekend combination:

• 1 or omitted: Saturday, Sunday
• 2: Sunday, Monday
• 3: Monday, Tuesday
• ...and so on...
• 11: Sunday only
• 12: Monday only
• 17: Saturday only

Imagine a team works on a Tuesday-Saturday schedule, with their weekend on Sunday and Monday. The weekend parameter would be 2:

Working Days (Tues-Sat Schedule) = 
NETWORKDAYS(Projects[StartDate], Projects[EndDate], 2, Holidays[Date])

This simple adjustment ensures your calculations accurately reflect the unique working schedules across different parts of your organization.

Common Mistakes to Avoid

While NETWORKDAYS is powerful, a few common issues can trip you up. Here's how to stay out of trouble:

• Incorrect Data Types: Ensure your start, end, and holiday date columns are all formatted as a "Date" or "Date/Time" type. If they're stored as text, Power BI won't be able to perform the calculation.
• Handling Blank Dates: What if a project hasn't finished yet, leaving the 'EndDate' blank? The formula will result in errors because NETWORKDAYS may return an error. You can prevent this by wrapping your calculation in an IF-statement to check for blanks first.

Working Days (Safer) =
IF(
    ISBLANK(Projects[EndDate]),
    BLANK(),
    NETWORKDAYS(Projects[StartDate], Projects[EndDate], 1, Holidays[Date])
)

This way, the column remains blank until an end date is entered, keeping your reports clean and error-free.

• Timezone Troubles: Be mindful if your data comes from a DateTime column with timezone information. It’s often best practice to convert these to a clean Date value to ensure consistency before using them in calculations.

Final Thoughts

Calculating working days is a fundamental skill for any Power BI user aiming to produce insightful business reports. By leveraging a dedicated date table, a holiday table, and the flexible NETWORKDAYS function, you can move beyond simple calendar math and create metrics that reflect how your business actually operates.

Creating one-off DAX calculations like this is just scratching the surface of true business intelligence. The real challenge comes from pulling together data from all your different platforms — your project management tool, CRM, ad platforms, and sales systems — to get a complete, real-time view of performance. That’s where we designed Graphed to help. You can connect your marketing and sales tools in seconds and then simply describe the dashboards and reports you need in plain English, turning hours of manual setup and frustration into a conversation.