Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

how to dynamically calculate the difference between 2 dates without counting bank holidays and we ?

Hello,

This url gives me the french bank holidays : https://calendrier.api.gouv.fr/jours-feries/metropole.json

I have a table with a datetime column called CreationDateTime. Can you please tell me how I can add a personnalized column that would calculate the duration between now and CreationDateTime, not counting the saturdays nor the sundays nor the bank holidays there could be in between ?

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    I have made up this solution which seems to work so far :

    ...
fnDuration = (startDate as datetime, endDate as datetime) =>
        let
            DurationDays = Duration.Days(endDate - startDate),
            ListDates = List.Dates(Date.From(startDate), DurationDays + 1, #duration(1, 0, 0, 0)),
            ListWeekDays = List.Select(ListDates, each Date.DayOfWeek(_, Day.Monday) < 5),
            ListHolidays = List.Transform(ListWeekDays, each Date.ToText(_, "yyyy-MM-dd")),
            Url = "https://calendrier.api.gouv.fr/jours-feries/metropole/" & Text.From(Date.Year(startDate)) & ".json",
            Holidays = Json.Document(Web.Contents(Url)),
            #"Converti en table" = Record.ToTable(Holidays),
            #"Colonnes supprimées" = Table.RemoveColumns(#"Converti en table",{"Value"}),
            HolidaysTrimmed = Table.ToList(#"Colonnes supprimées"),
            ListInterSect = List.Intersect({ListHolidays, HolidaysTrimmed}),
            Result = endDate - startDate - #duration(List.Count(ListInterSect) + DurationDays + 1 - List.Count(ListWeekDays), 0, 0, 0)
    in Result,
    AddedCustom = Table.AddColumn(#"Type modifié", "DiffDate", each fnDuration([sys_created_on], DateTime.FromText(DateTime.ToText(DateTime.LocalNow(), [Format="dd-MMM-yyyy HH:mm:ss", Culture="en-US"])))),
    #"Type modifié3" = Table.TransformColumnTypes(AddedCustom,{{"DiffDate", type duration}}),
...

     

5 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hello,

      Thank you but I was referring to do that in Power Query M language, do you know then please ?

      • CNENFRNL's avatar
        CNENFRNL
        Community Champion
        3 years ago
        let
    Source = Json.Document(Web.Contents("https://calendrier.api.gouv.fr/jours-feries/metropole.json")),
    Ferie = List.Transform(Record.FieldNames(Source), Date.From),

    Date_Start = #date(2017,2,7),
    Date_End = #date(2020,5,19),
    #"Excluded Ferie" = List.Difference(List.Dates(Date_Start, Duration.Days(Date_End - Date_Start) + 1, #duration(1,0,0,0)), Ferie),
    #"Excluded Weekend" = List.Count(List.Select(#"Excluded Ferie", each Date.DayOfWeek(_, Day.Monday)<5))
in
    #"Excluded Weekend"