Display latest data based on other fields

[Vysakh]

Hi all,

Below is a sample data set:

PO#	Invoice#	Date	Quantity
123	q123	01-02-2020	20
456	q456	02-02-2020	30
789	q789	03-04-2020	2
789	a789	05-04-2020	3
789	z789	06-04-2020	5

I want to display the latest data for each PO#, hence the result should be:

PO#	Invoice#	Date	Quantity
123	q123	01-02-2020	20
456	q456	02-02-2020	30
789	z789	06-04-2020	5

Any suggestion on how I can achieve this via DAX?

[amitchandak]

Measure =
VAR __id = MAX ( 'Table'[PO #] )
VAR __date = CALCULATE ( MAX( 'Table'[Date] ), ALLSELECTED ( 'Table' ), 'Table'[PO #] = __id )
RETURN CALCULATE ( sum ( 'Table'[Quantity] ), VALUES ( 'Table'[PO # ), 'Table'[PO #] = __id, 'Table'[Date] = __date )

[Mariusz · Accepted solution]

Hi @Vysakh 

You can create a measure like below 

Measure = 
VAR __maxDate = 
    CALCULATE(
        MAX( 'Table'[Date] ),
        ALL( 'Table' ),
        VALUES( 'Table'[PO#] )
    )
RETURN 
CALCULATE(
    INT( NOT ISEMPTY( 'Table' ) ),
    'Table'[Date] = __maxDate 
)

And use it as a visual filter like below

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn