how to dynamically retrieve previous working day ?

[Anonymous]

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 ?

[Vijay_A_Verma · Accepted solution]

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

[Anonymous]

Thank you !

I have a similar question here https://community.powerbi.com/t5/Power-Query/how-to-dynamically-calculate-the-difference-between-2-d... if you have a little time.