cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
FayeB1901
Helper I
Helper I

Report Last Non Blank Row

Hi there
I'm trying to create a calculated column that returns the last non blank value by a given date. I've tried several DAX options but can't quite get this working. See data below - in this example I would expect a value of -75.71 for 10/11/21.

FayeB1901_0-1638306414462.png

Thanks in advance
Faye


2 ACCEPTED SOLUTIONS
VahidDM
Super User
Super User

Hi @FayeB1901 

 

Try this:

Measure=
Var _A = max(table[Date])
If(isblank(max(table[value])),
calculate(max(table[value]),filter(all(table),table[Date]<_A&&table[value]<>blank())),
max(table[value])
)

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: 
www.linkedin.com/in/vahid-dm/

 

 

View solution in original post

Hi Vahid 

 

No unfortunately no good - not sure what is going on here with the calculation!

FayeB1901_0-1638320707322.png

 

View solution in original post

7 REPLIES 7
VahidDM
Super User
Super User

Hi @FayeB1901 

 

Try this:

Measure=
Var _A = max(table[Date])
If(isblank(max(table[value])),
calculate(max(table[value]),filter(all(table),table[Date]<_A&&table[value]<>blank())),
max(table[value])
)

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: 
www.linkedin.com/in/vahid-dm/

 

 

Hi Vahid 

 

No unfortunately no good - not sure what is going on here with the calculation!

FayeB1901_0-1638320707322.png

 

@FayeB1901 

Try this:

 

Meas1ure =
VAR _A =
    MAX ( table[Date] )
VAR _B =
    CALCULATE (
        MAX ( table[Date] ),
        FILTER ( ALL ( table ), table[Date] < _A && table[value] <> BLANK () )
    )
RETURN
    IF (
        ISBLANK ( MAX ( table[value] ) ),
        CALCULATE ( MAX ( table[value] ), FILTER ( ALL ( table ), table[Date] = _B ) ),
        MAX ( table[value] )
    )

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: 
www.linkedin.com/in/vahid-dm/

 

 

@Vahid got it - it was filter context - wanted ALLSELECTED not ALL. Thank you!

@VahidDM any ideas on how to calculate as a column? Those values are Lat/Lon that I need to plot on the map & ARCGIS won't work with a measure ...

@FayeB1901 

 

Try this to add a column:

Column=
VAR _A =
   [Date]
VAR _B =
    CALCULATE (
        MAX ( table[Date] ),
        FILTER ( ALL ( table ), table[Date] < _A && table[value] <> BLANK () )
    )
RETURN
    IF (
        ISBLANK ( [value] ),
        CALCULATE ( MAX ( table[value] ), FILTER ( ALL ( table ), table[Date] = _B ) ),
        [value] )
    )

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: 
www.linkedin.com/in/vahid-dm/

 

 

@VahidDM no not quite - seems like a filter context thing, like it's using a result not from the relevant ID maybe?

FayeB1901_0-1638394492028.png

 



Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors