Get last available value from previous dates if it's empty

Hi szymakat ,

Thanks for reaching out to the Microsoft fabric community forum.

You can efficiently address the issue of filling null values in Power BI by using DAX. Suppose your table includes date_c, project, and scope_km columns, and some scope_km values are blank. To forward-fill the last known value until a new one appears, follow these steps:

Start by entering your data in Power BI, including sample dates, project names (e.g., UNY_NYC_Manhattan), and scope_km values such as 476.62 and 500, with blanks in between. After loading the data, switch to “Data” view and create a new column with this DAX formula:

ValueWithPrevious =
VAR CurrentDate = ProjectData[date_c]
VAR CurrentProject = ProjectData[project]
RETURN
CALCULATE(
   MAX(ProjectData[scope_km]),
   FILTER(
       ProjectData,
       ProjectData[project] = CurrentProject &&
       ProjectData[date_c] <= CurrentDate &&
       NOT(ISBLANK(ProjectData[scope_km]))
   )
)

This formula dynamically retrieves the most recent non-blank value for each project up to the current date. After creating the column, add date_c, project, scope_km, and the new ValueWithPrevious column to your table visual. This method ensures all nulls are accurately forward-filled.

Please find the attached pbix and screenshort file for your reference.

Best Regards,
Tejaswi.
Community Support