Forum Discussion

Rich_Wyeth's avatar
Rich_Wyeth
Helper I
6 months ago
Solved

Negative difference in Days

HI,   I have the below to calculate Network days between two dates. However if it is a negative, it isn't returning the value of that, it just says ERROR. I can't figure out why, have I missed anyt...
Need Help
  • MFelix's avatar
    MFelix
    6 months ago

    Hi Rich_Wyeth ,

     

     

    Try the following code:

    try List.Count(List.Select(List.Dates([KPI_DATE], Duration.TotalDays([REC_DATE]-[KPI_DATE]) + 1, #duration(1,0,0,0)), each List.Contains({1,2,3,4,5}, Date.DayOfWeek(_))))
otherwise

- List.Count(List.Select(List.Dates([KPI_DATE], Duration.TotalDays([KPI_DATE]-[REC_DATE]) -1, #duration(1,0,0,0)), each List.Contains({1,2,3,4,5}, Date.DayOfWeek(_))))

     

ronrsnfld's avatar
ronrsnfld
Super User
6 months ago

It appears from your formula you are not taking Holidays into account. Given that,

here is one way of doing this that returns the same results as NETWORKDAYS in Excel:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31TcyMDJT0lEy0reAMGN1QOLmMHFDI31DsCJTpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [KPI_DATE = _t, REC_DATE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"KPI_DATE", type date}, {"REC_DATE", type date}}),
    
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Days Diff", each 
           [dts={[KPI_DATE],[REC_DATE]},
            first=List.Min(dts),
            last=List.Max(dts),
            diff=Duration.Days(last-first)+1,
            all=List.Dates(first,diff,#duration(1,0,0,0)),
            weekdays=List.Select(all, each Date.DayOfWeek(_)<>Day.Saturday and Date.DayOfWeek(_)<>Day.Sunday),
            networkdays=List.Count(weekdays),
            plusMinus=if [KPI_DATE] > [REC_DATE] then -1*networkdays else networkdays
            ][plusMinus], Int64.Type)
in
    #"Added Custom"