Filter rows without filtering date columns

[Hayleysea]

Hi There,

I have a matrix with dates for columns and ID's for rows and the values are text inside which only appear when an ID meets a certain condition (inputted into the raw data). 

The issue I am having is that I only want ID's to show where the condition is met (value has text) for the latest date column.

But if I filter on that condition based only on the latest week all my other date columns dissapear. 

Any ideas?

I was thinking whether there is a formula that would take the a value from the latest date and match against the ID in earlier dates so that I can filter based on that and that should get rid of the filter issue but I don't know what that formula would look like?

Thank you so much in advance

[amitchandak]

@Hayleysea ,

You have to something like that. More we can tell with Data

VAR __id = MAX ( 'Table'[id] )
VAR __date = CALCULATE ( MAX( 'Table'[date] ), ALLSELECTED ( 'Table' ),  'Table'[id] = __id ) 
RETURN CALCULATE ( MAX ( 'Table'[value] ), VALUES ( 'Table'[id] ), 'Table'[id] = __id, 'Table'[date] = __date )

[Hayleysea]

Thanks, data is like this so take the result from the latest date and populate against the matching ID for the other dates. I tried the formula from @amitchandak but didn't get the desired result.

ID	Date	Status	Result
A	1/01/2020	R	R
B	1/01/2020	R	A
C	1/01/2020	G	G
A	1/02/2020	G	R
B	1/02/2020	R	A
C	1/02/2020	G	G
A	1/03/2020	R	R
B	1/03/2020	A	A
C	1/03/2020	G	G

[v-frfei-msft · Accepted solution]

Hi @Hayleysea ,

Please create a calculated column as below.

Column = 
VAR maxdate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( 'Table', 'Table'[ID] = EARLIER ( 'Table'[ID] ) )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[Status] ),
        FILTER (
            'Table',
            'Table'[Date] = maxdate
                && 'Table'[ID] = EARLIER ( 'Table'[ID] )
        )
    )

For more details, please check the pbix as attached.

[TomMartens]

Hey @Hayleysea ,

please create a pbix file that contains sample data, but still reflects your data model. Upload the pbix file to onedrive or dropbox and share the link. Don't forget to describe the expected result.

Regards,

Tom