Forum Discussion
FayeB1901
Helper I
Helper IReport 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...
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 )
AlexisOlson
Super User
Super UserYou 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.
FayeB1901Helper I
Helper IHmm 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
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 )