Get last (=newest) non-blank value

[Anonymous]

This seems easy, but I haven't been able to do that 😞

I'm trying to get the most recent non-blank values from the following table (for two columns).

I need two measures, one for each column that satisfies this task.

The measures should work with the date filter to get the newest one.

Month End	This	That
31/08/2021 12:00:00 AM
31/07/2021 12:00:00 AM	T&S	M&E
30/06/2021 12:00:00 AM
31/05/2021 12:00:00 AM	SJ
30/04/2021 12:00:00 AM	OH	TM
31/03/2021 12:00:00 AM	TM	US
28/02/2021 12:00:00 AM	MJ	AU
31/01/2021 12:00:00 AM
31/12/2020 12:00:00 AM
30/11/2020 12:00:00 AM

Examples for different selected dates:

August or July: 

This = T&S

That = M&E

June:

This = SJ

That = TM

I have tried various dax such as firstnonblank or max, but couldn't achieve this.

Please help. Thanks.

[amitchandak]

@Anonymous , Have you tried a measure like?

measure 1 = lastnonblankvalue(Table[Month End]), Max(Table[This]))

measure 2= lastnonblankvalue(Table[Month End]), Max(Table[That]))

[Anonymous]

@amitchandak 

Yes, it tried it and it does not work 😞 

[PaulDBrown · Accepted solution]

See if this works:

First, the model:

Then the following measures:

Non Blank "This" = MAXX(FILTER(FactTable, FactTable[This] <> BLANK()), FactTable[This])

Non Blank "That" = MAXX(FILTER(FactTable, FactTable[ That] <> BLANK()), FactTable[ That])

"This" measure =
VAR ThisValue =
    CALCULATE (
        MAX ( 'Calendar Table'[Date] ),
        FILTER (
            ALL ( 'Calendar Table' ),
            'Calendar Table'[Date] <= MAX ( 'Calendar Table'[Date] )
                && NOT ( ISBLANK ( [Non Blank "This"] ) )
        )
    )
RETURN
    CALCULATE (
        LASTNONBLANK ( FactTable[This], FactTable[This] ),
        FILTER (
            ALL ( 'Calendar Table' ),
            'Calendar Table'[Date] <= MAX ( 'Calendar Table'[Date] )
                && 'Calendar Table'[Date] >= ThisValue
        )
    )

"That" Measure =
VAR _Value =
    CALCULATE (
        MAX ( 'Calendar Table'[Date] ),
        FILTER (
            ALL ( 'Calendar Table' ),
            'Calendar Table'[Date] <= MAX ( 'Calendar Table'[Date] )
                && NOT ( ISBLANK ( [Non Blank "That"] ) )
        )
    )
RETURN
    CALCULATE (
        LASTNONBLANK ( FactTable[ That], FactTable[ That] ),
        FILTER (
            ALL ( 'Calendar Table' ),
            'Calendar Table'[Date] <= MAX ( 'Calendar Table'[Date] )
                && 'Calendar Table'[Date] >= _Value
        )
    )

I've attached the sample PBIX file