Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 ?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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}}),
...

 

View solution in original post

5 REPLIES 5
CNENFRNL
Community Champion
Community Champion

CNENFRNL_0-1680033554676.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Anonymous
Not applicable

Hello,

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

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"

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Anonymous
Not applicable

Hello, it's kind of what I want, but the need is to have the duration in a new column of a table, being the time elapsed between the value in column CreationDateTime (which is a DateTime and not a Date) and now (including the time part). Do you know how to do that ?

Anonymous
Not applicable

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}}),
...

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors