Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Return correct status according to most recent entry.

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". 

4507101R Tuck11/05/2023 08:28:0011/05/2023 13:51:005.383333As Per Lead Engineer Jobsheet
4507101E Defew11/05/2023 10:44:0011/05/2023 13:37:002.883333Complete
4507101E Defew11/05/2023 08:00:0011/05/2023 10:44:002.733333Incomplete
4507101R Tuck10/05/2023 08:26:0010/05/2023 14:49:006.383333As Per Lead Engineer Jobsheet
4507101E Defew10/05/2023 09:56:0010/05/2023 14:44:004.8Incomplete
4507101J Dixon10/05/2023 09:52:0010/05/2023 12:02:002.166667As 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's avatar
    ERD
    3 years ago

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

18 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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. 

  • ERD's avatar
    ERD
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Anonymous , 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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        ERD
        Icon for Community Champion rankCommunity Champion
        3 years ago

        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 )