Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

how to dynamically retrieve previous working day ?

Hello,

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

Can you tell me how to write a function that takes a date as a parameter and returns the previous working day from the date in paramater (so not a week-end, and not one of the bank holidays contained in the above json) please ?

Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    3 years ago

    You can use this function

    = (InputDate)=>
let
    Source = Record.ToTable(Json.Document(Web.Contents("https://calendrier.api.gouv.fr/jours-feries/metropole.json"))),
    CalendarDates = List.Transform(Source[Name], each Date.From(_)),
    ListDates = List.Select(List.Dates(#date(1899,12,30), Duration.Days(Date.From(InputDate)-#date(1899,12,30)), #duration(1,0,0,0)), each Date.DayOfWeek(_,1)<5),
    PreviousWorkDay = List.Last(List.Difference(ListDates, CalendarDates))
in
    PreviousWorkDay

2 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    3 years ago

    You can use this function

    = (InputDate)=>
let
    Source = Record.ToTable(Json.Document(Web.Contents("https://calendrier.api.gouv.fr/jours-feries/metropole.json"))),
    CalendarDates = List.Transform(Source[Name], each Date.From(_)),
    ListDates = List.Select(List.Dates(#date(1899,12,30), Duration.Days(Date.From(InputDate)-#date(1899,12,30)), #duration(1,0,0,0)), each Date.DayOfWeek(_,1)<5),
    PreviousWorkDay = List.Last(List.Difference(ListDates, CalendarDates))
in
    PreviousWorkDay