Need to calculate Networkdays from datetime stamp

[Anonymous]

Hi Friends,

I need ur help to calculate the network days between two DateTime stamp. I have tried all solution which already provided in this forum but unfortunately, I'm unable to get the below output. Please advise me on this.

Opened date # 22/01/2021 18:15:33

Closed Date # 25/01/2021 18:04:44

Networkdays Output in decimal # 0.99

[Payeras_BI · Accepted solution]

Hi @Anonymous ,

Sure, find attached the PBIX file.

[Payeras_BI]

Hi @Anonymous ,

Ok. You can use Bill Szysz's Function fxSOWH (Sum of working hours) for your purposes.
More details here

Link to the original Excel file

As you tell me, for now, don't care about the Start and End of working hours I set it as below:

Then just changed the Sum of working hours format from Hour to General and got what you are looking for:

[Anonymous]

Thank you for this solution. Can you please guide me on how to implement this in the Power BI Desktop?

[Payeras_BI]

Hi @Anonymous ,

Given both "Opened Date" and "Closed Date" always fall on Working days and you have a list of Holidays like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLSNzDUNzIwMlQwtLAyNLUyNlbSUTIyRRY1MLEyMVGKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Opened date" = _t, #"Closed date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Opened date", type datetime}, {"Closed date", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each 
        let 
        ListOfDates = List.Dates(Date.From([Opened date]),Number.From(Date.From([Closed date])-Date.From([Opened date])),#duration(1,0,0,0)),
        ListOfHolidays = List.Intersect({Holidays,ListOfDates}),
        ListOfWeekends = List.Select(ListOfDates, each Date.DayOfWeek(_,1) > 4 ),
        ListOfDatestoSubstract = List.Distinct(List.Combine({ListOfHolidays,ListOfWeekends})),
        DaystoSubstract = List.Count(ListOfDatestoSubstract)
        in
        Duration.TotalDays([Closed date]-[Opened date]) - DaystoSubstract)
in
    #"Added Custom"

[Anonymous]

Thank you for the solution. However, I have received the above error message. Also, at some time Opened date/Closed date will be a Weekend date

[Payeras_BI]

Hi @Anonymous ,

Re the error received make sure you convert your Holiday table to a list before using it in your code.

Re "Sometimes Opened date/Closed date will be a Weekend date", provide one of these examples and the expected output.

Do you also plan to take into account the Working hours?

[Anonymous]

Hi,

As requested, Please find the below examples were ticket opened & Closed on Weekend days.