networking days from positive and negative duration

Hi Etienne_1995 ,

Please try this way. The difference in the calculated values is because we are located in different regions, so there is a time difference resulting in a day's difference, please ignore these differences.
Use this M function to create a custom column:

let 
   Start = if Number.From([OTD]) < Number.From([WE]) then [OTD] else [WE],
   End = if Number.From([OTD]) < Number.From([WE]) then [WE] else [OTD],
   DateList = List.Dates(Start, Number.From(End - Start) + 1, #duration(1,0,0,0)),
   FilteredDateList = List.Select(DateList, each Date.DayOfWeek(_, Day.Monday) < 5),
   Result = List.Count(FilteredDateList)
in 
   if [Differenz in Tagen] >= 0 then Result else (0 - Result)

The final output is as below:

And here is all of the M function in the Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtY31zcyMDJR0gEyDSHMWJ1oJSN9Q2OEhCkREigmGRkMoATMG7EA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [WE = _t, OTD = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"WE", type date}, {"OTD", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Differenz in Tagen", each Duration.Days([WE] - [OTD])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Differenz in AT", each let 
   Start = if Number.From([OTD]) < Number.From([WE]) then [OTD] else [WE],
   End = if Number.From([OTD]) < Number.From([WE]) then [WE] else [OTD],
   DateList = List.Dates(Start, Number.From(End - Start) + 1, #duration(1,0,0,0)),
   FilteredDateList = List.Select(DateList, each Date.DayOfWeek(_, Day.Monday) < 5),
   Result = List.Count(FilteredDateList)
in 
   if [Differenz in Tagen] >= 0 then Result else (0 - Result)),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Anzahl", each [Differenz in AT] - [Differenz in Tagen])
in
    #"Added Custom2"

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

Hi Etienne_1995,

if you'd like to know every single step --> delete this part of code and expand the record.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDXNzDWNzIwMlHSUTIwhHNidaKVDI31DYzgcqYIuVgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [WE_Datum = _t, #"Termin OTD" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"WE_Datum", type date}, {"Termin OTD", type date}}, "sk-SK"),
    Ad_AnzahlWochenendtage = Table.AddColumn(ChangedType, "Anzahl Wochenendtage", each
        [ d1 = List.Max({[WE_Datum], [Termin OTD]}),
          d2 = List.Min({[WE_Datum], [Termin OTD]}),
          dates = List.Dates(d2, Duration.TotalDays(d1-d2), #duration(1,0,0,0)),
          networkdDays = List.Select(dates, each Date.DayOfWeek(_, Day.Monday) < 5),
          networkDaysCount = List.Count(networkdDays)
        ][networkDaysCount], Int64.Type)
in
    Ad_AnzahlWochenendtage