Get last non blank value ordered by date

[tokhir]

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

Amount

156209596
129549185
125600180
146158302
137078645
124467960
Null
Null
...

I need get last non blank amount value but filtered with date. If i use dax to get last amount value without any filter, it is ordered from smallest to largest

[Anonymous · Accepted solution]

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

[SachinNandanwar]

Please note that LASTNONBLANK('TableName'[YourValueColumn], 1) will return values based on the natural sort order and not on last value in the column.

[tokhir]

got it. Is it possible to get the latest value using simple dax? Or do I need to use a slightly complex query using date?

[SachinNandanwar]

Try something like this 

Meausre =
        CALCULATE(
             SUM(Table[Amount]),
             LASTNONBLANK(Table[Date], SUM(Table[Amount]))
         )

[Ankur04]

Hi @tokhir ,

can you try something like this, please let me know if your requirement is different.

Non blank date = VAR maxdate = CALCULATE(MAX('Table (10)'[Date]),'Table (10)'[Amount] <> BLANK())
RETURN CALCULATE(SUM('Table (10)'[Amount]),'Table (10)'[Date] =  maxdate)

Thanks,

[Visharavana]

@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)

[PhilipTreacy]

@tokhir 

Sorry your request is not clear.  What do you mean "If i use dax to get last amount value without any filter, it is ordered from smallest to largest" ?

Presumably these columns are in the same table?  If so, why show them separately?

Please show a clear example of the result you want.

Regards

Phil

[tokhir]

Yes, they are both in the same table
If i use LASTNONBLANK('TableName'[YourValueColumn], 1) to get last non blank value from my numeric column its return the biggest one. I dont know why. For test purposes i paste this column to table and yes, its in smallest to largest order not in the format in initial table with date
I just need last non blank value from numeric column in order like in my table(i showed above the example)