Return a filtered table values for each category as per Latest Date

[Anonymous]

Hi, I have a scenraio where in the excel table, there are records on various dates for each category. I want to retrieve only those rows having the the latest date for that category within the table. 

See below example: 

I wish to return only these two as per the latest Date for their category. Is this possible using DAX? I need to use Entity, Customer and Project columns as they will be constant. 

I am also trying to work this out but would appreciate any advise. 
Below is the test data.

Date	Entity	Customer	Project	Stage
14 January 2021	Mining	ABC	Test Project	X1
19 February 2021	Mining	ABC	Test Project	X2
04 March 2021	Mining	ABC	Test Project	X3
23 December 2020	Excavation	XYZ	Live Project	K1
12 January 2021	Excavation	XYZ	Live Project	K2
10 March 2021	Excavation	XYZ	Live Project	K3

Below is what I want to achieve.

Date	Entity	Customer	Project	Stage
04 March 2021	Mining	ABC	Test Project	X3
10 March 2021	Excavation	XYZ	Live Project	K3

[v-henryk-mstf · Accepted solution]

Hi @Anonymous ,

According to your needs, I did the following test. The created measure is used to calculate the value corresponding to each latest date. This calculation logic is used in the virtual table created later. Then create a virtual table, get the expected result.

Result = 
SUMMARIZE (
    'Table',
    'Table'[Entity],
    'Table'[Customer],
    'Table'[Project],
    "Stage",
        CALCULATE (
            MAX ( 'Table'[Stage] ),
            FILTER ( ALL ( 'Table' ), 'Table'[Customer] = MAX ( 'Table'[Customer] ) )
        ),
    "Date",
        CALCULATE (
            MAX ( 'Table'[Date] ),
            FILTER ( ALL ( 'Table' ), 'Table'[Customer] = MAX ( 'Table'[Customer] ) )
        )
)

M =
CALCULATE (
    MAX ( 'Table'[Date] ),
    FILTER ( ALL ( 'Table' ), 'Table'[Customer] = MAX ( 'Table'[Customer] ) )
)

Here is the sample .pbix file link.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Many thanks @v-henryk-mstf . Exactly what I was looking for but was not able to figure out the DAX. Thanks for your help.

[mohammedadnant]

Hi @Anonymous 

Good day,

Check this video, it is similar concept, instead of marks you can use date.

https://youtu.be/THDmCmSozt8

Thanks & Regards,

Mohammed Adnan

https://www.youtube.com/c/taik18

[Anonymous]

Thanks for quick reply @mohammedadnant I am testing this and initial results look good. ALLEXCEPT is bit difficult to comrehend. If I were to return Entity, Customer, Project and Stage for the max date, should I include these columns in the ALLEXCEPT function?

[mohammedadnant]

@Anonymous 

yes, you can add those.

Thanks & Regards,

Mohammed Adnan