Forum Discussion

FayeB1901's avatar
FayeB1901
Icon for Helper I rankHelper I
4 years ago
Solved

Report Last Non Blank Row as a column

Hi All    Putting this one out there again as I wasn't able to get a working solution. I've got a measure to do this, but I need  a calculated column. This is because the data is latitudes which AR...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    You want the last nonblank prior to the date in the current row?

     

    Add an extra condition to your MaxDate calculation:

    Last Lat =
VAR CurrDate = Table1[Date]
VAR MaxDate =
    CALCULATE (
        MAX ( Table1[Date] ),
        ALLEXCEPT ( Table1, Table1[ID] ),
        NOT ( ISBLANK ( Table1[Lat] ) ),
        Table1[Date] <= CurrDate
    )
RETURN
    CALCULATE (
        MAX ( Table1[Lat] ),
        ALLEXCEPT ( Table1, Table1[ID] ),
        Table1[Date] = MaxDate
    )
FayeB1901's avatar
FayeB1901
Icon for Helper I rankHelper I
4 years ago

Hmm close but not quite, this returns last non blank row overall, not the last non blank row based on the current row, if that makes sense?

 

AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
4 years ago

You want the last nonblank prior to the date in the current row?

 

Add an extra condition to your MaxDate calculation:

Last Lat =
VAR CurrDate = Table1[Date]
VAR MaxDate =
    CALCULATE (
        MAX ( Table1[Date] ),
        ALLEXCEPT ( Table1, Table1[ID] ),
        NOT ( ISBLANK ( Table1[Lat] ) ),
        Table1[Date] <= CurrDate
    )
RETURN
    CALCULATE (
        MAX ( Table1[Lat] ),
        ALLEXCEPT ( Table1, Table1[ID] ),
        Table1[Date] = MaxDate
    )