Max Date Relative To Job Number

[Anonymous]

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 Number)

Table 1

Job Number

Job 1

Job 2

Job 3

Table 2

Job Number	Original Planned Date
Job 1	01/01/2020
Job 2	01/02/2020
Job 3	01/03/2020

Table 3

Job Number	Current Planned Date
Job 1	06/01/2020
Job 1	07/01/2020
Job 2	02/02/2020
Job 2	04/02/2020
Job 2	08/02/2020
Job 3	01/03/2020
Job 3	08/03/2020

What I need to end up with is a new table with the following Job Number, Original Planned Date, the latest Current Planned Date, and a column showing slip:

Table 4

Job Number	Original Planned Date	Current Planned Date	Slip
Job 1	01/01/2020	07/01/2020	6
Job 2	01/02/2020	08/02/2020	7
Job 3	01/03/2020	08/03/2020	7

Any help that people can give will be greatly appreciated.

[v-lili6-msft]

hi  @Anonymous 

If you have created the relationship among these tables by [Job number] column?

https://docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships

If yese, you could use this formula to create a new table:

Table 4 = 
SUMMARIZE (
    'Table 1',
    'Table 1'[Job Number],
    "Original Planned Date", CALCULATE ( MAX ( 'Table 2'[Original Planned Date] ) ),
    "Current Planned Date", CALCULATE ( MAX ( 'Table 3'[Current Planned Date] ) ),
    "Slip", DATEDIFF (
        CALCULATE ( MAX ( 'Table 2'[Original Planned Date] ) ),
        CALCULATE ( MAX ( 'Table 3'[Current Planned Date] ) ),
        DAY
    )
)

If no, you could use this formula to create a new table

Table 5 = 
SUMMARIZE (
    'Table 1',
    'Table 1'[Job Number],
    "Original Planned Date", CALCULATE (
        MAX ( 'Table 2'[Original Planned Date] ),
        FILTER ( 'Table 2', 'Table 2'[Job Number] = 'Table 1'[Job Number] )
    ),
    "Current Planned Date", CALCULATE (
        MAX ( 'Table 3'[Current Planned Date] ),
        FILTER ( 'Table 3', 'Table 3'[Job Number] = 'Table 1'[Job Number] )
    ),
    "Slip", DATEDIFF (
        CALCULATE (
            MAX ( 'Table 2'[Original Planned Date] ),
            FILTER ( 'Table 2', 'Table 2'[Job Number] = 'Table 1'[Job Number] )
        ),
        CALCULATE (
            MAX ( 'Table 3'[Current Planned Date] ),
            FILTER ( 'Table 3', 'Table 3'[Job Number] = 'Table 1'[Job Number] )
        ),
        DAY
    )
)

and here is sample pbix file, please try it.

Regards,

Lin

[Anonymous]

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]

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

[amitchandak]

@Anonymous ,Assuming table 1 is Job master. try like

Table 4 =
summarize(Table1, table[job],table2[Original Planned Date], "Current Planned Date",max(Table3[Current Planned Date]),"Slip",
datediff(min(Table3[Current Planned Date]),max(Table3[Current Planned Date]),day))

[Greg_Deckler]

This will get you the MAX Current Planned Date: https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/td-p/985814

[Anonymous]

Greg,

Thanks for your help, sorry to be a pain but with my limted knowledge i'm not sure to to impliment this to get the final result i'm after.

Could you explain a bit futher or show how it would be applied relative to the examples tables I gave please.

Best Regards

Alex