Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
v-eqin-msft
Community Support
Community Support

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:

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
v-eqin-msft
Community Support
Community Support

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:

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.

Anonymous
Not applicable

Thank you, this worked. 

amitchandak
Super User
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.