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).
Syntax:
NETWORKDAYS (<start date>, <end date> [, <weekend>, <holidays>])Parameters
| Term | Definition |
|---|---|
| start_date | A 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_date | A 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. |
| weekend | Indicates 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 |
| holidays | A 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=>
let
DateList=List.Dates(StartDate,Number.From(EndDate-StartDate)+1,#duration(1,0,0,0)),
//create series of Dates
RemoveWeekends=List.Select(DateList,each Date.DayOfWeek(_,Day.Monday)<5),
//remove weekends
RemoveHolidays=List.RemoveItems(RemoveWeekends, HolidayList),
//remove holidays
Countdays=List.Count(RemoveHolidays)
//count days
in
Countdays- 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.
