Working Days Between Two dates in two Tables

[DanielHolland]

Hello Guys, 

I have two Tables, Table 1 contains an "Open Date" , 

Table 2 Contains "Escalated Date"  

I would like to calculate the workings days which have passed between the "Open Date" and "Escalated Date" 

Looking online its really complicated creating indexes and extendeted date tables. is there a simple way to do this? 

Cheers 

Dan 

Table 1

Opened Date

15/04/2022

Table 2
Escalated Date	Working Days Passed
18/04/2022	1

[Vijay_A_Verma]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)

Code for Table1 (Make sure that you name this query to Table1 as it gets referred with this name in Table2)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjU1UtJRMtE3NNU3MlKK1YlWMjc3A4sYG4BFYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Ticket ID" = _t, #"Opened Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Ticket ID", Int64.Type}, {"Opened Date", type date}})
in
    #"Changed Type"

Code for Table2

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjU1UtJRMtE3tNA3MjAyUorViVYyNzcDixkbQMViAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Ticket ID" = _t, #"Escalated Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Ticket ID", Int64.Type}, {"Escalated Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Opened Date", each Table1{[Ticket ID=[Ticket ID]]}[Opened Date]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Working Days Passed", each List.Sum(List.Transform(List.Dates([Opened Date],Duration.Days([Escalated Date]-[Opened Date]),#duration(1,0,0,0)),each (1-Number.From(Text.Contains("SatSun",Text.Start(Date.DayOfWeekName(_),3))))))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Opened Date"})
in
    #"Removed Columns"

[jennratten]

How are you matching the records between the two tables for which the date difference needs to be calculated?  What is considered to be a working date?

[DanielHolland]

My Apologies i should of mentioned i have a Common "Ticket ID" Field 

Working Days are Monday to Friday

Table 1
Ticket ID	Opened Date
552	15/04/2022

Table 2
Ticket ID	Escalated Date	Working Days Passed
552	18/04/2022	1

[jennratten]

To further develop the previous solution I provided, if you have scenario in which there is a ticket ID with an end date that does not exist in the start date table, without any other error handling the value would return an error, shown below in the Days Elapsed column.  If you wanted to specify a different (number) value to be returned instead of an error, you could implement a try-otherwise statement, which would return the specified number, shown below in the Days Elapsed2 column.

SNIP (Ticket 3 exists in the EndDate table but not in the StartDate table)

SCRIPT

let

    SampleStartDates = Table.TransformColumnTypes(Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLWN7LQNzIwMlKK1YlWMgKKmOibQAViAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Ticket = _t, #"Start Date" = _t]), {{"Start Date", type date}}),
    SampleEndDates_AllRecordsMatch = Table.TransformColumnTypes(Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLRN9E3MjAyUorViVYyAgsYGyBEjIEipvqGUIFYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Ticket = _t, #"End Date" = _t]),{{"End Date", type date}}),
    // New table with a record that does not have a start date.
    SampleEndDates_WithExtraRecord = Table.TransformColumnTypes(Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLRN9E3MjAyUorViVYyAgsYGyBEjIEipvqGUIFYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Ticket = _t, #"End Date" = _t]),{{"End Date", type date}}),
    DaysElapsed_WithoutHandlerForExtraRecord = Table.AddColumn (SampleEndDates_WithExtraRecord, "Days Elapsed", each 
        Duration.Days (
            // The value of the End Date column from the current table.
            [End Date] - 
            // The value of the Start Date column 
            // for the first row with a matching Ticket, from the other table.
            Table.SelectRows(SampleStartDates, (x)=> x[Ticket]=[Ticket]){0}[Start Date]
        ), Int64.Type 
    ),
    DaysElapsed_WithHandlerForExtraRecord1 = Table.AddColumn (DaysElapsed_WithoutHandlerForExtraRecord, "Days Elapsed2", each 
        try Duration.Days (
            // The value of the End Date column from the current table.
            [End Date] - 
            // The value of the Start Date column 
            // for the first row with a matching Ticket, from the other table.
            Table.SelectRows(SampleStartDates, (x)=> x[Ticket]=[Ticket]){0}[Start Date] 
        ) otherwise 0, Int64.Type 
    )
in
    DaysElapsed_WithHandlerForExtraRecord1

[DanielHolland]

Thank you I do appreciate your help. 

This looks extreamly complicated, im not sure I can get this to work withing my Current PBIX Report. 

I know this is a Big Ask, Please may I have an Example PBIX File I can download to try and reverse engineer? 

I would upload mine but it has alot of personal in formation etc

[jennratten · Accepted solution]

Here is another file that is simplified with the table and column names matching your examples so it should be easier for you to follow.  I have also included step-by-step instructions of what to do to integrate this with your script (text in green font shown in the snip below).  Below is a snip from the Advanced Editor view of Table2.  Essentially you will copy/paste into your query and then make a couple of edits.

[DanielHolland]

Thank you so so much this has helped massivley, Apologies on the novice Questions

[jennratten]

You are very welcome!  We have all been there.  🙂

[jennratten]

No problem at all.  Please see the attached file.  If you need any help please let us know.

[jennratten]

This is how you can calculate the number of days elapsed between the start and end dates when they reside in different tables.  Paste the script below into a blank query.  This contains both sample tables with the result added to the sample end dates table.  If you view the query in the advanced editor or view the step script in the formula bar you will see the comments I added for various steps to explain what is going on in the script.

Note - this could further be enhanced to handle situations in which a ticket appears with an end date but not with a start date, if the start date is after the end date, etc. 

SCREENSNIP

SCRIPT

let

    SampleStartDates = Table.TransformColumnTypes(Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLWN7LQNzIwMlKK1YlWMgKKmOibQAViAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Ticket = _t, #"Start Date" = _t]), {{"Start Date", type date}}),
    SampleEndDates = Table.TransformColumnTypes(Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLRN9E3MjAyUorViVYyAgsYG0BFYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Ticket = _t, #"End Date" = _t]),{{"End Date", type date}}),
    DaysElapsed = Table.AddColumn (SampleEndDates, "Days Elapsed", each 
        Duration.Days (
            // The value of the End Date column from the current table.
            [End Date] - 
            // The value of the Start Date column 
            // for the first row with a matching Ticket, from the other table.
            Table.SelectRows(SampleStartDates, (x)=> x[Ticket]=[Ticket]){0}[Start Date]
        ), Int64.Type 
    )
in
    DaysElapsed