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

Your accepted answer from MFelix  seems to give an incorrect result for the dates

 

7Jan 2026 to 15 Dec 2025

 

I think it should be -18 but accepted answer from MFelix  returns -16 if I implemented it correctly.

 

My Implementation:

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 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(_)))))
in
    #"Added Custom"