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

 

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'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

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    1 year ago

    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's avatar
      tokhir
      Frequent Visitor
      1 year ago

      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)

  • Visharavana's avatar
    Visharavana
    Resolver 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)
  • Ankur04's avatar
    Ankur04
    Resolver II
    1 year ago

    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,

     

  • SachinNandanwar's avatar
    SachinNandanwar
    Impactful Individual
    1 year ago

    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's avatar
      tokhir
      Frequent Visitor
      1 year ago

      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's avatar
        SachinNandanwar
        Impactful Individual
        1 year ago

        Try something like this 

         

         

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