Forum Discussion

tokhir's avatar
tokhir
Frequent Visitor
1 year ago
Solved

Get last non blank value ordered by date

I have two column: Date and Amount columns Date 01.11.2024 02.11.2024 03.11.2024 04.11.2024 05.11.2024 06.11.2024 07.11.2024 08.11.2024 09.11.2024 10.11.2024   Am...
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi tokhir ,

    You can create a measure as below to get it:

    Last non blank value = 
VAR _maxdate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( 'Table', NOT ( ISBLANK ( 'Table'[Amount] ) ) )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[Amount] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] = _maxdate )
    )

    Best Regards

Visharavana's avatar
Visharavana
Icon for Resolver II rankResolver II
1 year ago

tokhir If I understand your question correctly, this is one of the solutions.

Last Non-Blank Amount by Date = 
VAR LastDateWithAmount = 
    MAXX(
        FILTER(
            'Table',
            NOT(ISBLANK('Table'[value]))
        ),
        'Table'[Date]
    )
RETURN
LOOKUPVALUE('Table'[value], 'Table'[Date], LastDateWithAmount)