Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
@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))
This will get you the MAX Current Planned Date: https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/td-p/985814
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
68 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |