Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Max Date Relative To Job Number

I have 3 Tables:   Table 1 - List of Unique Job Numbers Tabel 2 - List of Job Numbers With Original Planned Date Table 3 - List of Job Numbers With Current Planned Date (Multiple Entries Per Job ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thanks for your reply, I have tried the first example you provided which is nearly what I want but doesn't give quite the right result.

 

In "Table 4" the "Current Planned Date" column retruns the same vale for every job number rather than the max date relative to that job.

 

Do you know how I could change this?

v-lili6-msft's avatar
v-lili6-msft
Community Support
6 years ago

hi  Anonymous 

Do you create a relationship between table1 and table3 by job number column as below?

 

and also try this formula:

Table 6 = 
SUMMARIZE (
    'Table 1',
    'Table 1'[Job Number],
    "Original Planned Date", MAXX(RELATEDTABLE('Table 2'),[Original Planned Date]),
    "Current Planned Date", MAXX(RELATEDTABLE('Table 3'),[Current Planned Date]),
    "Slip", DATEDIFF (
        MAXX(RELATEDTABLE('Table 2'),[Original Planned Date]),
        MAXX(RELATEDTABLE('Table 3'),[Current Planned Date]),
        DAY
    )
)

 

Regards,

Lin