Take the most recent value, which is not blank

Question

I have a task to create a column in a table visual in Power BI, which will always show the most recent value, regardless of what is selected in the date slicers.

Consider this dataset:

Site	MonthYr	KPI Value
Alfa	12/1/2023	100
Alfa	1/1/2024	99.6
Alfa	2/1/2024
Beta	12/1/2023	70
Beta	1/1/2024
Beta	2/1/2024
Gama	12/1/2023	60
Gama	1/1/2024	50

We have different sites, and their results for different KPIs. The most recent value result for KPI 1 should be for site Alfa 99.6, for Beta - 70, and for Gama - 50, even if they are for different months.

Currently, I have a measure, which takes the last value - for Feb.2024, but it has some blanks in it. I want to check for the last non-blank value, even if it was like 6 months ago.

Thank you in advance!

Answer 1

You can use a measure like below-

max_kpi = 

VAR max_date = 
CALCULATE(
    max(table_name[MonthYr]),
    FIlter(
        ALLEXCEPT(table_name,table_name[Site]),
        NOT ISBLANK( table_name[KPI Value])
    )
)

RETURN 
CALCULATE(
    MAX(table_name[KPI Value]),
    FILTER(
        ALLEXCEPT(table_name,table_name[Site]),
        table_name[MonthYr] = max_date
    )
)

Output