Dax Function: NETWORKDAYS
Category: Date and Time Functions
The NETWORKDAYS function in Power BI is used to calculate the number of working days (excluding weekends and optionally holidays) between two dates.
Purpose:
To determine the effective number of business days between two dates.
Type of Calculations:
It performs date-based calculations by excluding non-working days like weekends and holidays.
Practical Use Cases:
- Project management: Calculate workdays for tasks or timelines.
- Employee scheduling: Determine working days for payroll or attendance.
- Business analysis: Calculate operational days for supply chain or service delivery.
NETWORKDAYS(start_date, end_date [, holidays])
| Parameter | Type | Description |
|---|---|---|
start_date | Datetime | The start date for the calculation. |
end_date | Datetime | The end date for the calculation. |
holidays | Table/Array (optional) | A list of holiday dates to exclude from the calculation. |
How Does NETWORKDAYS Dax Function Works
- The function calculates the total days between
start_dateandend_date. - It removes weekends (Saturdays and Sundays) by default from the total.
- If a list of
holidaysis provided, it further excludes these dates from the working days count.
Mathematically:
Working Days = Total Days – Weekends – Holidays- The function calculates the total days between
What Does It Return?
- Type: Integer.
- Meaning: Returns the count of working days (Monday to Friday by default) between
start_dateandend_date, excluding weekends and any specified holidays.
When Should We Use It?
- To calculate deadlines or delivery dates considering only working days.
- For employee payroll processing based on workdays.
- To determine business activity days within a specific period.
Examples
Basic Usage
Calculate the working days between two dates:
WorkDays = NETWORKDAYS(DATE(2025, 2, 1), DATE(2025, 2, 28))
Output: Returns 20, assuming no holidays.
Column Usage:
Add a calculated column to determine working days for each project:
ProjectDays = NETWORKDAYS(Project[StartDate], Project[EndDate])
For a project starting on 2025-02-01 and ending on 2025-02-10, the result is 6.
Advanced Usage
Exclude specific holidays from the calculation:
WorkDaysWithHolidays = NETWORKDAYS(
DATE(2025, 2, 1),
DATE(2025, 2, 28),
{"2025-02-14", "2025-02-21"}
)
Output: Returns 18, as February 14 and February 21 are excluded as holidays.
Tips and Tricks
- Always format
start_dateandend_dateas datetime for accurate calculations. - Use a predefined holiday table for consistency in excluding holidays across multiple calculations.
- For non-standard weekends (e.g., Friday and Saturday), use custom logic or Power Query for adjustments.
Potential Pitfalls
- Omitting the
holidaysparameter when holidays are critical to the calculation can lead to incorrect results. - Be cautious of date formats; mismatched formats might cause errors or incorrect outputs.
- This function doesn’t support custom weekend days natively (e.g., if weekends are not Saturday-Sunday).
Performance Impact of NETWORKDAYS DAX Function:
- Using holiday lists stored as tables rather than hardcoding improves maintainability and performance for large datasets.
- Precomputing the holiday exclusions in Power Query can further optimize complex models.
Related Functions You Might Need
- WORKDAY: Calculates the date after adding a specific number of working days to a given start date.
- NETWORKDAYS.INTL: Similar to NETWORKDAYS but allows customization of weekends.
- DATEDIFF: Calculates the total difference (including weekends) between two dates.
- TODAY: Retrieves the current date, often used in dynamic date range calculations.
Want to Learn More?
For more information, check out the official Microsoft documentation for NETWORKDAYS You can also experiment with this function in your Power BI reports to explore its capabilities.
Unlock the full capabilities of Power BI and elevate your data insights with our specialized consulting services. Whether you need guidance on advanced DAX functions like those highlighted here, support in designing interactive dashboards, or expertise in optimizing data models for enhanced performance, our experienced Power BI consultants are equipped to deliver customized solutions for your business. Explore our Power BI Consulting Services page to discover how we can help your organization make smarter, data-driven decisions.
The NETWORKDAYS function calculates the number of working days (excluding weekends and optionally holidays) between two dates.
No, the standard NETWORKDAYS function assumes Saturday and Sunday as weekends. Use NETWORKDAYS.INTL for custom weekend configurations.
The function returns a negative value representing the number of working days in reverse.
Holidays are provided as a list of date values (table or array) that the function excludes from the working days count.
Yes, the function includes both start_date and end_date as part of the working days calculation.
Established in 2020, Lets Viz Technologies provides a full range of high-quality data analysis and data visualization services. We are also an authorized Zoho Partner.
Sitelinks
