Forum Discussion
Calculate days between 2 columns in different table
Super UserHi,
In Table1, write this calculated column formula
=lookupvalue('Table 2'[Early Finish Date],'Table 2'[ID],'Table 1'[ID])
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
You are welcome. Please share realistic data and show the expected result.
Thanks Ashish. Here goes...
Here are cut down versions of the 2 tables, with Table 2 after merging the Early Finish Date from Table 1. Common field is the ID.
Model - Table 1
ID (the common field) Owner Reporting Month Early Finish Date Model - Table 2
ID (the common field) Activtiy Description Reporting Month Early Start Date Early Finish Date (merged from Table1) DateDiff (weeks) These are the current result when a Visiual Table is created:
from Table2 from Table 2 from Table 1 from Table 2 from Table 2 from Table 2 from Table 2 Row ID Activity Description Owner Reporting Month Early Finish (merged from Table 1) Early Start DateDiff (weeks) 1 5 Feedback Jane Apr22 11/9/23 4/1/24 16 2 5 Feedback Jane May22 11/10/23
4/1/24 12 3 5 Feedback Jane Jun22 10/11/23 4/1/24 8 4 5 Feedback Jane Jul22 2/9/24 4/1/24 -35 5 21 Update Review Bob Jun22 20/7/23 4/6/25 98 6 21 Update Review Bob Jul22 24/7/23 4/6/25 97 7 36 Endorsement Kate Jun22 3/2/23 3/8/22 26 8 36 Endorsement Kate Jul22 3/2/23 5/8/22 26 The visual is providing a row for every item.
Expected result is to only show the most recent for the latest Reporting month ie only 1 row for ID 5, 21, 36, (row 4, 6 and 😎 rather than all.
- Ashish_Mathur
I cannot help you with this information. Keep it simple - share Table 1, Table 2 and show the expected result.