Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Return Most Recent Entry In Summary Table

Reposting after tidying up previous post. 

 

In the table below, I have duplicate entries. I am only interested in the most recent entry, as per the "Finish Time" column. In my summary table, I am only interested to see the "Work Order No" and the "Status". The only two status entries I am interested in is "Complete" and "Incomplete".

 

Work Order NoResource NameStart TimeFinish TimeTime on Site - DecimalStatus
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

 

My Current DAX: 

 

 

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

 

 

This returns the most recent entry:

 

Work Order NoStatus
4507101As Per Lead Engineer Jobsheet

 

 I need to tweak this code for it to exclude the status "As Per Lead Engineer Jobsheet" - not interested in it. I was thinking about  MAX - 1 ... however, I can have a case where I have "As Per Lead Engineer Jobsheet" back-to-back etc. 

 

I appreciate any help on this please. 

 

 

3 Replies

  • some_bih's avatar
    some_bih
    Community Champion
    2 years ago

    Hi Anonymous one possible solution: create new table Test as following
    If this post helps, accept as solution to help other members find it more quickly. Kudos appreciated.

    Test=

    VAR __max_finish_date = [MaxFinish] --calculating max finish time
    VAR __summarize_table_temp =
    SELECTCOLUMNS (
    FILTER (
    SUMMARIZE (
    'Aeromark Data',
    'Aeromark Data'[Work Order No],
    'Aeromark Data'[Resource Name],
    'Aeromark Data'[Start Time],
    'Aeromark Data'[Finish Time],
    'Aeromark Data'[Time on Site - Decimal],
    'Aeromark Data'[Status]
    ),
    'Aeromark Data'[Status] = "Complete"
    || 'Aeromark Data'[Status] = "Incomplete"
    && 'Aeromark Data'[Finish Time] = __max_finish_date
    ),
    "work Order No", 'Aeromark Data'[Work Order No],
    "Status", 'Aeromark Data'[Status]
    )
    RETURN
    __summarize_table_temp

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi, 

       

      Thanks, this doesn't return anything

       

      I get 'The value for 'MaxFinish' cannot be determined. Either the column doesn't exist, or there is no current row for this column.'

  • some_bih's avatar
    some_bih
    Community Champion
    2 years ago

    Hi Anonymous 

    I would say, as I remember, [MaxFinish] should be measure like below. Please create one

    MaxFinish=MAX ( 'Aeromark Data'[Finish Time] )