Forum Discussion

spb's avatar
spb
Frequent Visitor
4 years ago

Calculate days between 2 columns in different table

Looking to calculate the difference in the 2 dates.

 

Table1

'ID' 'Late Finish date'

1      10 May 22

 

Table 2

'ID' 'Early Finish Date'

1      20 May 22

 

Answer - 10 days.

To be included in a Table visual with the ID column, both date columns and the days different column.

 

cheers

Need Help

11 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    spb , if you have ID column, which is in common in both, then create a common ID table

     

    ID= distinct(union(distinct(Table1[ID]),distinct(Table2[ID])))

     

    Join with both tables on ID and create measure like

     

    Sumx(Values(ID[ID]), calculate(datediff(min(Table1[Late Finish date]), Max(Table2[early Finish date]), Day) ))

    • spb's avatar
      spb
      Frequent Visitor
      4 years ago

      I was unaware of the 'Union' function. I note that the function mentions the 2 tables need to be the same structure ie have the same number of columns. The 2 tables have a common ID field in one of the columns, but have other different columns and number of columns in each table. 

       

      Given this, presumably the solution could not apply in this case.

       

      Look forward to understand a workaround.

      Thanks

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    4 years ago

    If both tables have one ID per row, why not merge them in the query editor. Finding the difference between the two columns in same table can be done easily with a power query or DAX column.

     

    Pat

     

    • spb's avatar
      spb
      Frequent Visitor
      4 years ago

      Thanks mahoneypat.

      I successfully merged the extra column into one of the existing tables and then used datddiff as suggested,  which produced a result.

      However, when created a table visualisation I noticed I get multiple results for the same ID. This is due to the fact that updated data is added every reporting month to the table with the same ID (theres a reporting month field in one of the tables too). How do I calculate the difference in early start and early finish for the most recent month only to show in a table visualiation?

      Hopefully Ive explained this ok.

      Thanks for insight.

       

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    Would you be OK with writing a calculated column formula in Table1 to bring ovr data from Column 2 of Table 2?

    • spb's avatar
      spb
      Frequent Visitor
      4 years ago

      Yes that would be ideal as Union fucnction not available and merging the 2 large tables may cause other unknown issues. 

      Any tips on the creaion of the calculated column you refer to?

      Much appreciated.

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        4 years ago

        Hi,

        In Table1, write this calculated column formula

        =lookupvalue('Table 2'[Early Finish Date],'Table 2'[ID],'Table 1'[ID])