How to "jump" from a column's last record to the across data on another column?

[Anonymous]

Hello everyone,

Today, I would like to share the most specific issue with you that I've encountered.

I'm trying to calculate the open lead time of each ongoing order in the production unit of my organization.

I would like you to have a look at the below table to understand my issue.

Order Number	Step ID	Last Transaction Date of The Step	Transaction's Scheduled End Date
AXBY9001	1	6/11/2021	4/4/2022
AXBY9001	2	6/11/2021	4/4/2022
AXBY9001	3	6/11/2021	4/6/2022
AXBY9001	4	6/14/2021	4/8/2022
AXBY9001	5	6/14/2021	4/11/2022
AXBY9001	6	6/22/2021	4/12/2022
AXBY9001	7	7/9/2021	4/14/2022
AXBY9001	8	7/9/2021	4/20/2022
AXBY9001	9	7/9/2021	4/21/2022
AXBY9001	10	7/12/2021	4/22/2022
AXBY9001	11	7/22/2021	4/25/2022
AXBY9001	12	7/23/2021	4/25/2022
AXBY9001	13	7/23/2021	4/26/2022
AXBY9001	14	7/23/2021	4/27/2022
AXBY9001	15	7/23/2021	4/28/2022
AXBY9001	16	7/23/2021	4/28/2022
AXBY9001	17	7/26/2021	4/29/2022
AXBY9001	18	8/10/2021	5/3/2022
AXBY9001	19	8/11/2021	5/4/2022
AXBY9001	20		5/6/2022
AXBY9001	21		5/9/2022
AXBY9001	22		5/11/2022
AXBY9001	23		5/11/2022
AXBY9001	24		5/13/2022

As you can see, the "Last Transaction Date" column shows the actual end date of the order's steps.

However, the Production planning team has already calculated each end date of the order's every step on the Transaction's Scheduled End Date before production started.

Here is the need to calculate Open Lead Time;

The duration between the "last record" of the scheduled end date column (5/13/2022 can be calculated via MAXX function) and the date which is opposite of the "last record" of the last transaction date of the step column.

The red one is also calculated via the MAXX function. However, I need only to jump the opposite of the last transaction date column's last record somehow which is 5/4/2022. After that, I will be able to calculate the duration between these two dates (orange ones).

I hope it would be understandable for you and any guidance, comment can help me a lot.

I appreciate your answers in advance.

Best

[ryan_mayu · Accepted solution]

@Anonymous 

pls try this

Measure = DATEDIFF(maxx(FILTER('Table','Table'[Order Number]=max('Table'[Order Number])&&'Table'[Last Transaction Date of The Step]=max('Table'[Last Transaction Date of The Step])),'Table'[Transaction's Scheduled End Date]),max('Table'[Transaction's Scheduled End Date]),DAY)

[wdx223_Daniel · Accepted solution]

Open_Lead_Time=VAR _max_Schedule_End_Date=CALCULATE([Transaction's Scheduled End Date],ALLEXCEPT(FactTable,FactTable[Order Number])) VAR _max_Actual_End_Date=CALCULATE([Last Transaction Date of The Step],ALLEXCEPT(FactTable,FactTable[Order Number])) RETURN IF([Last Transaction Date of The Step]=_max_Actual_End_Date,_max_Schedule_End_Date-_max_Actual_End_Date)

[Anonymous]

Hello Daniel,

Your solution works also correctly...

Thank you for your big support!

[ryan_mayu · Accepted solution]

@Anonymous 

pls try this

Measure = DATEDIFF(maxx(FILTER('Table','Table'[Order Number]=max('Table'[Order Number])&&'Table'[Last Transaction Date of The Step]=max('Table'[Last Transaction Date of The Step])),'Table'[Transaction's Scheduled End Date]),max('Table'[Transaction's Scheduled End Date]),DAY)

[Anonymous]

Unfortunately, It doesn't work. I think it was my mistake because I didn't share all rows of the table with you. Completely, there is more than one order number in this table. And when I try to check the equality of Order number and MAX(Order Number), it did not work. Would it be the problem?

Thanks in advance

Best regards.

[ryan_mayu]

@Anonymous

it should work. I added another order number and had a test.

pls see the attachment below

[Anonymous]

That was my mistake, your solution is correct my mate!

You made my day. I wish that I have a chance to order a beer for you... 🙂

I really thank you very much...

[ryan_mayu]

you are welcome 🙂