cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
soliverc
Frequent Visitor

Get most recent non-null value?

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

 

soliverc_0-1638802718177.png

 

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

1 ACCEPTED SOLUTION
Eyelyn9
Community Support
Community Support

Hi @soliverc ,

 

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:

Eyelyn9_1-1639028732085.png

 

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.

View solution in original post

3 REPLIES 3
Eyelyn9
Community Support
Community Support

Hi @soliverc ,

 

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:

Eyelyn9_1-1639028732085.png

 

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.

soliverc
Frequent Visitor

Thank you, this worked. 

amitchandak
Super User
Super User

@soliverc ,

Max non blank date

 

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

 

or

 

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



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors