Direct Query question - latest date

[andra2]

Hello, 

I am using direct query for a table having let's say these values:

Team  Strategy  Stock   Section   Created On

X	1	C	1	1/27/2022 1:14
X	1	C	2	1/27/2022 1:15
X	1	C	3	1/27/2022 1:16
X	1	C	4	1/27/2022 1:17
X	2	C	1	1/26/2022 1:18
X	2	C	2	1/26/2022 1:13
X	2	C	3	1/26/2022 1:12
X	2	C	4	1/26/2022 1:14

I am trying to display in a matrix table only the latest values for same team + strategy + stock combination, no matter the section ID.  In the above case I should have displayed in the matrix table just these 2 rows:

Team    Strategy   Stock   Section    Created On

X	1	C	4	1/27/2022 1:17
X	2	C	1	1/26/2022 1:18

Can you, please, let me know how can I showcase this within a measure?

Thank you!

[Anonymous]

if you have a date table you can use today funtion or you can use max function 

[andra2]

and how today or max function will help? can you, please, write the measure I should be using?

[v-yiruan-msft · Accepted solution]

Hi @andra2 ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Create a measure as below to judge if it is the latest created on date group by  team, strategy and stock

Flag = 
VAR _latestdate =
    CALCULATE (
        MAX ( 'Table'[Created On] ),
        ALLEXCEPT ( 'Table', 'Table'[Team], 'Table'[Strategy], 'Table'[Stock] )
    )
RETURN
    IF ( SELECTEDVALUE ( 'Table'[Created On] ) = _latestdate, 1, 0 )

2. Apply a visual-level filter with condition "Flag is 1" to the table/matrix visual

Best Regards