show the most recent info

[jwi1]

hi all,

I have the following question about below table. 

As you can see there are muliple rowes with the same container nr, but with different order dates ans status codes:

container_nr	status_cod	order_dat
SUDU5168064	113	26-7-2018
SUDU5168064	110	17-8-2018
SUDU5168064	115	7-6-2018
SUDU5168064	100	8-9-2017
APRU5773334	110	12-10-2016
APRU5773334	115	20-12-2016

I need a formula/ filter which gives me only the container number with the most recent order date.

So, the result of the formula/ filter gives me only one row per container nr. (as shown below).

Thanks! John

container_nr	status_cod	order_dat
SUDU5168064	110	17-8-2018
APRU5773334	115	20-12-2016

[TomMartens · Accepted solution]

Hey,

I created a calculated column in your table using this DAX statement like so:

most recent = 
var thisContainer = 'Table2'[container_nr]
var lastDateByContainerNr = 
    CALCULATE(
        MAX('Table2'[order_dat])
        ,FILTER(
            ALL('Table2')
            ,'Table2'[container_nr] = thisContainer
        )
    )
return
IF('Table2'[order_dat] = lastDateByContainerNr, "yes", "no")

This leads to this table:

Now you can use the column "most recent" to filter your data.

Hopefully this is what you are looking for!

Regards,

Tom