Return last value that is not blank in a column

[Anonymous]

Hello everyone,

I have a table with this form:

PROJECT       FINANCIAL_DATE          METRIC           CALENDARISTIC_DATE      [CALCULATED_COLUMN]

x                            x                              x                                 x                                       (solution)

In the METRIC column i have some projects that have blank values for that specific calendaristic date. In the [CALCULATED_COLUMN] i want to bring the last populated value looking in the previous dates, and if there is no value, put 0 instead.

I have tried various formula combinations with lastnonblank, earlier, max but i couldn't seem to get it working.

Help me please,

Thank you.              

[Anonymous · Accepted solution]

I am pretty sure i have found the solution, if someone else is interested:

[CALCULATED_COLUMN]=
VAR CurrentData=TABLE[Date]
Return
IF(TABLE(METRIC)=BLANK(),
CALCULATE(LASTNONBLANK([METRIC],[METRIC]),FILTER(ALLEXCEPT(TABLE,[PROJECT]),[DATE]<=CurrentData)),[METRIC])