How To Calculate Networkdays In Power Bi

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:

1. 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.

2. 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).

3. 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:

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.