cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

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

 

Table 1

Job Number
Job 1
Job 2
Job 3

 

 

Table 2

Job NumberOriginal Planned Date
Job 101/01/2020
Job 201/02/2020
Job 301/03/2020

 

 

Table 3

Job NumberCurrent Planned Date
Job 106/01/2020
Job 107/01/2020
Job 202/02/2020
Job 204/02/2020
Job 208/02/2020
Job 301/03/2020
Job 308/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 NumberOriginal Planned DateCurrent Planned DateSlip
Job 101/01/202007/01/20206
Job 201/02/202008/02/20207
Job 301/03/202008/03/20207

 

Any help that people can give will be greatly appreciated.

6 REPLIES 6
Highlighted
Super User IV
Super User IV

Re: Max Date Relative To Job Number

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


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Super User IV
Super User IV

Re: Max Date Relative To Job Number

@TypeR ,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))

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Regular Visitor

Re: Max Date Relative To Job Number

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

Highlighted
Community Support
Community Support

Re: Max Date Relative To Job Number

hi  @TypeR 

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Regular Visitor

Re: Max Date Relative To Job Number

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?

Highlighted
Community Support
Community Support

Re: Max Date Relative To Job Number

hi  @TypeR 

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

10.JPG

 

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Kudoed Authors