Exclude Non-Working Days from your Project’s Power Bi Dashboard

Scroll down
Mohamed sabbah
Mohamed sabbah
  • Residence:
    Saudi Arabia
  • City:
  • Age:

February 1, 2023



In Power BI, when data is imported from Primavera, the duration of activities may include weekends and holidays, which can be a problem. This could result in incorrect project timelines and misaligned resource allocation, leading to potential project delays or resource utilization issues. To address this, it may be necessary to create a custom formula in Power BI to exclude weekends and holidays from the duration calculation, or to make adjustments in the data source in Primavera before importing into Power BI.

To solve this issue, we found two methods to exclude the non-working days from the Calanders or any calculation in our modules.

First Solution: Using DAX

With DAX, in the past, we often had to create a calendar table with consecutive dates to filter and count the desired data based on the date table. But in the latest version of Power BI, we can avoid building this calendar table and simply use the ‘NETWORKDAYS’ function to calculate the number of working days. This feature was introduced in the July 2022 Feature Summary for Power BI.

Check here for more details Power BI July 2022 Feature Summary.

NETWORKDAYS’ is used to return the whole number of working days between two dates (inclusive).


NETWORKDAYS (<start date>, <end date> [, <weekend>, <holidays>])


start_dateA date that represents the start date. The dates for which the difference is to be computed. The start_date can be earlier than, the same as, or later than the end_date.
end_dateA date that represents the end date. The dates for which the difference is to be computed. The start_date can be earlier than, the same as, or later than the end_date.
weekendIndicates the days of the week that are weekend days and are not included in the number of whole working days between start_date and end_date. Weekend is a weekend number that specifies when weekends occur.
Weekend number values indicate the following weekend days:
1 or omitted: Saturday, Sunday
2: Sunday, Monday
3: Monday, Tuesday
4: Tuesday, Wednesday
5: Wednesday, Thursday
6: Thursday, Friday
7: Friday, Saturday
11: Sunday only
12: Monday only
13: Tuesday only
14: Wednesday only
15: Thursday only
16: Friday only
17: Saturday only
holidaysA column table of one or more dates that are to be excluded from the working day calendar.

Second Solution: Using M Query

Currently, there is no similar function like ‘NETWORKDAYS’ in the M syntax that can be used directly. However, we can construct a custom function to achieve a similar goal.

  • Firstly, we will create a table contains all holidays and duplicate it, after that convert the new table to list as shown.
  • Secondly, add new blank query we will call it “DurationwithoutHolidays” and write the below code.
(StartDate as date, EndDate as date, HolidayList as list) as number=>
    //create series of Dates
    RemoveWeekends=List.Select(DateList,each Date.DayOfWeek(_,Day.Monday)<5),
    //remove weekends
    RemoveHolidays=List.RemoveItems(RemoveWeekends, HolidayList),
    //remove holidays
    //count days
  • Thirdly, add custom column and call the new query we created in the previous step and congratulations you now have the durations without the weekends nor the holidays.

Download Ful Sample

Posted in Uncategorized
Write a comment
© 2022 All Rights Reserved.
Write me a message