Forum Discussion
Max Date Relative To Job Number
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
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?
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