Forum Discussion

DanielHolland's avatar
DanielHolland
Frequent Visitor
4 years ago
Solved

Working Days Between Two dates in two Tables

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 DateWorking Days Passed
18/04/20221
Need Help
Tips and Tricks
  • jennratten's avatar
    jennratten
    4 years ago

    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.

     

     

10 Replies

  • jennratten's avatar
    jennratten
    Super User
    4 years ago

    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's avatar
      DanielHolland
      Frequent Visitor
      4 years ago

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

       

      Working Days are Monday to Friday

       

      Table 1 
      Ticket IDOpened Date
      55215/04/2022

       

      Table 2   
      Ticket IDEscalated DateWorking Days Passed
      55218/04/20221
      • jennratten's avatar
        jennratten
        Super User
        4 years ago

        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

         

         

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

    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"