Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Get most recent non-null value?

I have a table like this. Notice the blank values towards the bottom.

 

 

I would like to get the value of the latest date, but the value can't be null.

My DAX formula is bringing back the value of the latest date (19/12/2021) which is null, however I want to bring back the latest non-null value, which is for the date 21/11/2021.

Here is what I have tried so far:

 

 

 

 

 

 

Latest Value = 
CALCULATE(
    // get sum of value column
    SUM('Table1'[Value]), 
    // where value is not blank, and date is max date
    'Table1'[Value] <> BLANK() && Table1[Date] = MAX(Table1[Date])
)

 

 

 

 

I get the value: null / blank

 

I thought this should bring back the figure 305? Because my conditions are:

where value is not null AND where date = max date

Shouldn't the max date now be 21/11/21 because the nulls have been removed?

Another piece of DAX I've tried, using the filter function.

 

 

 

 

 

 

Latest Value = CALCULATE(
    SUM('Table1'[Value]),
    FILTER(ALL('Table1'),
    'Table1'[Value] <> BLANK()
    &&
    'Table1'[Date] = MAX('Table1'[Date]))

 

 

 

 

 

 

I get the same result: null

 

Where am I going wrong? I think it's something to do with my max date section.

Unfortunately all file hosters are blocked in work, so I can't share this dummy file. However I hope I have simplified the data enough to just quickly get the idea. 

 

If there is an attachment button, please let me know because I can't find it. 

 

Many thanks

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    1.If one date has one value, you could simply use LASTNOTBLANK():

    Measure = LASTNONBLANK('Table'[Value],MAX('Table'[Date]))

     

    2. If you want to get the sum of the last date, please try to get the last and value<> blank date firstly and then sum:

    Measure 2 = CALCULATE(SUM('Table'[Value]),FILTER('Table',[Date]=MAXX(FILTER('Table',[Value]<>BLANK()),[Date])))

     

    Output:

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    1.If one date has one value, you could simply use LASTNOTBLANK():

    Measure = LASTNONBLANK('Table'[Value],MAX('Table'[Date]))

     

    2. If you want to get the sum of the last date, please try to get the last and value<> blank date firstly and then sum:

    Measure 2 = CALCULATE(SUM('Table'[Value]),FILTER('Table',[Date]=MAXX(FILTER('Table',[Value]<>BLANK()),[Date])))

     

    Output:

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you, this worked. 

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous ,

    Max non blank date

     

    Maxx(filter(Table, not(isblank(Table[Value])) , Table[Date]))

     

    or

     

    Maxx(filter(allselected(Table), not(isblank(Table[Value])) , Table[Date]))