networkdays between dates

[Etienne_1995]

Hi Power BI Community

I have the following problem with calculating networkdays:

I have the custom column:

let
originalDuration = [Differenz in Tagen],
listDates = {
Int64.From( [WE_Datum] ) .. Int64.From( [Termin OTD] )
},
subtract = List.Transform(
listDates,
( i ) => let
itemDate = Date.From( i ),
listWeekends = not List.IsEmpty(
List.Select( { 6, 7 }, each _ = Date.DayOfWeek( itemDate, Day.Monday ) + 1 )
),
listHolidays = not List.IsEmpty(
List.Select( Holidays[Date], each _ = itemDate )
)
in
if listWeekends or listHolidays then true else null
),
adjustedDuration =
if originalDuration >= 0 then
originalDuration - List.NonNullCount( subtract )
else
originalDuration + List.NonNullCount( subtract )
in
Duration.From( adjustedDuration )

I am using this custom column in the query editor in Power BI (Version May 2023).
So I'm trying to get only the networking days between two dates, without the weekend.

The thing is, that it works in one way but doesn't in the other.

Here you can see the problem I face. When I have a negative difference, the weekeknd days are calculated correct. But when the difference is positive, the output is always 0. And I don't know what is wrong with the calculated column.

The expected output in the yellow highligted fields would be 2 (2 weekend days). So the difference should not be 6, it should be 4 (in the column "Differenz in AT", networkingdays). And the output in the green highligted rows have to stay the same, because they are correct.

The column "Anzahl Wochenendtage" count weekenddays is just a help-column to check the correct output.

Any help would be appreciated.

[Anonymous]

Hi, @Etienne_1995 

Can you provide a sample data that does not contain privacy? I tried to look at the screenshots you provided, but the data part is not clear, so that it is difficult for me to see what the problem is based on your M code. 

In addition, you can troubleshoot the following two points:
1. Make sure your M code logic is correct and correctly calculates the date of the non-working day. You can split your M code into multiple columns to determine which part of the calculation error.
2. Try using a simple dataset to check the reason for your Mcode calculation error.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.