Calculate days between 2 columns in different table

Frequent Visitor

4 years ago

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.

Forum Discussion