Forum Discussion
Get last available value from previous dates if it's empty
Hello,
would anyone have an idea how to fetch lastest available value if for certain date is null?
I'd need to repeat 476.62 for days with no result.
If on some point it changes to 500 and next days have nulls - i want to populate 500 then, up till today.
Thanks so much for your time!
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
3 Replies
Please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Do not include sensitive information and do not include anything that is unrelated to the issue or question.
Need help uploading data? click here
Want faster answers? click here
If this helped, please consider giving kudos and mark as a solution
me in replies or I'll lose your thread
consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
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 SupportThis works perfectly! Thank you so much for your effort.
