Return correct status according to most recent entry.

[Shak1254]

I have a summary table that I want to just see order numbers in this example "4507101" and next to it a status eg "Complete or Incomplete".  For these work order numbers I have a number of entries as you can see below, I am only interested in the most recent one, as per the date/time in column 4. 

The next issue is, if the most recent entry has the status "As Per Lead Engineer Jobsheet", I'm not interested in this. I want it to return the most recent "Complete" or "Incomplete". 

I was thinking of MAX - 1 date, but I could have back to back "As Per Lead Engineer Jobsheet". 

4507101	R Tuck	11/05/2023 08:28:00	11/05/2023 13:51:00	5.383333	As Per Lead Engineer Jobsheet
4507101	E Defew	11/05/2023 10:44:00	11/05/2023 13:37:00	2.883333	Complete
4507101	E Defew	11/05/2023 08:00:00	11/05/2023 10:44:00	2.733333	Incomplete
4507101	R Tuck	10/05/2023 08:26:00	10/05/2023 14:49:00	6.383333	As Per Lead Engineer Jobsheet
4507101	E Defew	10/05/2023 09:56:00	10/05/2023 14:44:00	4.8	Incomplete
4507101	J Dixon	10/05/2023 09:52:00	10/05/2023 12:02:00	2.166667	As Per Lead Engineer Jobsheet

So far I have the DAX: 

Table2 = SUMMARIZE('Table1','Table1'[Work Order No],"Status", CALCULATE(MAX('Table1'[Status] ),FILTER('Table1','Aeromark Data'[Finish Time] = MAX('Table1'[Finish Time]))))

However this does not exclude "As Per Lead Engineer Jobsheet" status"

[ERD]

@Shak1254 , you can try this measure:

Measure =
VAR dt =
    CALCULATE (
        MAX ( 'Table'[Finish Time] ),
        'Table'[status] <> "As Per Lead Engineer Jobsheet",
        ALL ( 'Table'[Finish Time] )
    )
VAR last_status = CALCULATE ( MAX ( 'Table'[status] ), 'Table'[Finish Time] = dt )
RETURN
    last_status

[Shak1254]

Hi, 

This works in terms of getting rid of "As Per Lead Engineer Jobsheet" thank you. 

However, it does not eliminate duplicates. So if I have two "Incomplete" for the same order number on different dates, it returns both results. I'm only interested in the most recent incomplete. 

[ERD]

Hi, I don't know what's your resulting visual, you can try this:

Measure = 
VAR dt =
    CALCULATE (
        MAX ( 'Table'[Finish Time] ),
        'Table'[status] <> "As Per Lead Engineer Jobsheet",
        ALL('Table')
    )
VAR last_status = CALCULATE ( MAX ( 'Table'[status] ), 'Table'[Finish Time] = dt )
RETURN
    IF ( MAX ( 'Table'[Finish Time] ) = dt, last_status )

[Shak1254]

My result

[Shak1254]

Should this code be embedded within a 'Summarise'? As now when I try in a new column, I'm only returning a complete or blank status. 

[ERD]

@Shak1254 , the code I've provided is for a measure, not a calculated column. 

Slight adjustment for the measure:

Measure = 
VAR dt =
    CALCULATE (
        MAX ( 'Table'[Finish Time] ),
        'Table'[status] <> "As Per Lead Engineer Jobsheet",
        ALL('Table'[Finish Time])
    )
VAR last_status = CALCULATE ( MAX ( 'Table'[status] ), 'Table'[Finish Time] = dt )
RETURN
    IF ( MAX ( 'Table'[Finish Time] ) = dt, last_status )

Calculated column:

Column = 
var ord_num = 'Table'[order number]
VAR dt =
    CALCULATE (
        MAX ( 'Table'[Finish Time] ),
        'Table'[status] <> "As Per Lead Engineer Jobsheet",
        'Table'[order number] = ord_num,
        ALL('Table')
    )
VAR last_status = CALCULATE ( MAX ( 'Table'[status] ), 'Table'[Finish Time] = dt )
RETURN
    last_status

[Shak1254]

Hi @ERD  this is very close! However on some instances it does not work. I have noted one as an example below. 

These two are the same work order no, so under 'StatusReal' I should not have two blanks, but one returning the correct status. 

[ERD]

@Shak1254 , without having the data, I am not able to reproduce the issue. Try to reproduce it in the demo table adding some values..

[Shak1254]

This is the data. For some of these your solution works, for others not so well. 

Date/Time	Work Order No	Resource Name	Start Time	Finish Time	Time on Site - Decimal	Status	Start Travel	Finish Travel	Travel Time - Decimal
30/08/2023 00:00:00	4651678	M Whincop	30/08/2023 12:08:00	30/08/2023 14:32:00	2.4	As Per Lead Engineer Jobsheet	30-08-23 09:40	30-08-23 12:08	2.4667
30/08/2023 14:32:00	4651678	S Irving	30/08/2023 12:09:00	30/08/2023 14:32:00	2.383333	Complete	30-08-23 09:45	30-08-23 12:09	2.4
21/08/2023 13:17:00	4633479	NMitchell	21/08/2023 09:17:00	21/08/2023 13:17:00	4	Incomplete	null	null	null
21/08/2023 00:00:00	4633479	N Phillips	21/08/2023 08:16:00	21/08/2023 09:31:00	1.25	As Per Lead Engineer Jobsheet	null	null	null
24/05/2023 00:00:00	4507102	W Trustrum	24/05/2023 00:00:00	24/05/2023 15:50:00	15.8333333333	As Per Lead Engineer Jobsheet	null	null	null
24/05/2023 13:37:00	4507102	B Jones	24/05/2023 13:22:00	24/05/2023 13:37:00	0.25	Complete	24-05-23 11:08	24-05-23 13:22	2.2333
16/05/2023 00:00:00	4507099	G Swann	16/05/2023 08:11:00	16/05/2023 17:32:00	9.35	As Per Lead Engineer Jobsheet	null	null	null
16/05/2023 16:57:00	4507099	M Barnard	16/05/2023 15:38:00	16/05/2023 16:57:00	1.316667	Complete	null	null	null

[ERD]

Well, the problem here is due to the same finish time.

Column = 
var ord_num = 'Table'[Work Order No]
VAR dt =
    CALCULATE (
        MAX ( 'Table'[Finish Time] ),
        'Table'[status] <> "As Per Lead Engineer Jobsheet",
        'Table'[Work Order No] = ord_num,
        ALL('Table')
    )
VAR last_status = CALCULATE ( MAX ( 'Table'[status] ), 'Table'[Finish Time] = dt, 'Table'[status] <> "As Per Lead Engineer Jobsheet" )
RETURN
    last_status

[Shak1254]

Yeah that I understand. I've found another instance though. 

Here, I only have the statuses "complete" "incomplete". I should be returning the most recent by finish time status. 

This turns up a blank. 

[ERD · Accepted solution]

I do see a value:

Tested with the latest code option:

Column = 
var ord_num = 'Table'[Work Order No]
VAR dt =
    CALCULATE (
        MAX ( 'Table'[Finish Time] ),
        'Table'[status] <> "As Per Lead Engineer Jobsheet",
        'Table'[Work Order No] = ord_num,
        ALL('Table')
    )
RETURN 
    IF( 'Table'[Finish Time] = dt && 'Table'[status] <> "As Per Lead Engineer Jobsheet", 'Table'[Status])

[Shak1254]

ERD I think we're onto a winner. Thank you very much!!! 

[ERD]

Demo data, please, or the file. Unfortunately, I cannot guess all the particularities of the data you have.

Or, you can also try this option:

Column = 
var ord_num = 'Table'[Work Order No]
VAR dt =
    CALCULATE (
        MAX ( 'Table'[Finish Time] ),
        'Table'[status] <> "As Per Lead Engineer Jobsheet",
        'Table'[Work Order No] = ord_num,
        ALL('Table')
    )
RETURN 
    IF( 'Table'[Finish Time] = dt && 'Table'[status] <> "As Per Lead Engineer Jobsheet", 'Table'[Status])

[shak1701]

https://docs.google.com/spreadsheets/d/18NcrXxbjP4wgUbzj4W1D1gqfNn4dK9YZ/edit?usp=drive_link&ouid=10...

Here you go. I've had to switch to my personal PC to share the link. This is the data I'm working with. 

Thank you. 

[ERD]

The access is restricted.

[shak1701]

This should work now. 

https://docs.google.com/spreadsheets/d/18NcrXxbjP4wgUbzj4W1D1gqfNn4dK9YZ/edit?usp=drive_link&ouid=10...

[Shak1254]

I reposted this after cleaning it up, but I can't seem to see the new post and don't see the option to delete this one.