Report Last Non Blank Row as a column

[FayeB1901]

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 ARC GIS can't plot in a map without having an individual row context. 

Options include:

1. A calculated column
2. Possibly a summary table?

Any feedback appreciated. See summary table below showing the problem. The highlighted values are pulling the last non blank value in the data set so suspect there is some DAX missing to delineate between IDs, could it be that simple? 

Thanks
Faye

[FayeB1901]

Thank you, appreciate this 🙂

[AlexisOlson]

You can certainly get the last Lat per ID.

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

The ALLEXCEPT here is specifying that we want to match only the ID column.

[FayeB1901]

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 · Accepted solution]

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
    )