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...
Need Help
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])

    • spb's avatar
      spb
      Frequent Visitor
      4 years ago

      Thanks Ashish this produces a result, although reveals I have an issue hadn't considered. I get multiple results once put into a table.

       

      As the 2 tables have new data added to it every month for the same ID number which can contains new Early Finish or Late Finish date. 

      So how to capture only the latest month for the calculation?

      I also tried an alternative data merge to bring across one column to the other table and then used a Datdiff measure which also produced the results, but again with mutiple results for the same ID once putting into a table.

      Any idea how i would only dispaly the current month?

      Thank you

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

        You are welcome.  Please share realistic data and show the expected result.