Calculating Networkdays in Power BI
Power BI does not have a built-in function to directly calculate networkdays. However, you can achieve this by using DAX formulas in Power BI. Here’s how you can do it:
- Create a calculated table to list all the public holidays:
Public Holidays = CALENDAR(DATE(2022, 1, 1), DATE(2022, 12, 31))
This formula creates a table with all the dates from January 1, 2022, to December 31, 2022.
- Create a calculated column in your main table to identify weekdays:
Weekday = WEEKDAY([Date])
This formula assigns a number to each date, representing the weekday (1 for Sunday, 2 for Monday, and so on).
- Create a measure to calculate the networkdays:
Networkdays = CALCULATE( COUNTROWS('Main Table'), NOT('Main Table'[Weekday] IN {1, 7}), NOT('Main Table'[Date] IN VALUES('Public Holidays'[Date])) )
This formula counts the number of rows in the ‘Main Table’ that are not weekends (Sunday or Saturday) and not public holidays.
Here’s an example to illustrate the calculation:
Date | Weekday |
---|---|
2022-01-01 | Saturday |
2022-01-02 | Sunday |
2022-01-03 | Monday |
2022-01-04 | Tuesday |
2022-01-05 | Wednesday |
In this example, assuming January 1st is a public holiday, the networkdays would be 2 (January 3rd and January 4th are weekdays).
By following these steps and customizing the formulas according to your data, you can calculate the networkdays in Power BI.