Lastnonblank / Fill Down with DAX Measure

[polestar_11]

I need your help: I’m trying to use a measurement (not a calculated column) to fill up the last value of the previous months in the months with empty values until a value appears again. Can you help me with the DAX Measure? I^ve tried it with the following Measure but the result was not right:

Lastnonblankvalue = 
CALCULATE(LASTNONBLANKVALUE('Date'[Date],SUM('Sales'[Value])))

It should look like:

Appreciate your help!

Information Dataset:

[Mikelytics · Accepted solution]

Hi @polestar_11 

Please try the following:

data model:

Formula:

Repeat Last Value = 

var __SelectedDate = EOMONTH(MAX(Dim_Date[PK_Date]),0)

var __LastDateWithValue =
CALCULATE(
    MAX(Fact_Sales[Date]),
    FILTER(
        ALL(Dim_Date),
        [PK_Date]<=__SelectedDate
    )
)

RETURN

CALCULATE(
    TOTALMTD([Aggregation | Sum],Dim_Date[PK_Date]),
    Dim_Date[PK_Date] = __LastDateWithValue
)

Result

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

[Mikelytics · Accepted solution]

Hi @polestar_11 

Please try the following:

data model:

Formula:

Repeat Last Value = 

var __SelectedDate = EOMONTH(MAX(Dim_Date[PK_Date]),0)

var __LastDateWithValue =
CALCULATE(
    MAX(Fact_Sales[Date]),
    FILTER(
        ALL(Dim_Date),
        [PK_Date]<=__SelectedDate
    )
)

RETURN

CALCULATE(
    TOTALMTD([Aggregation | Sum],Dim_Date[PK_Date]),
    Dim_Date[PK_Date] = __LastDateWithValue
)

Result

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

[polestar_11]

@Mikelytics Thank you for the answer. I've tried your solution but it is no as expected:

I am not sure if I changed the "TOTALMTD([Aggregation | SUM]" right.

[Mikelytics · Accepted solution]

Hi @polestar_11 

I think I found out the reason. Please add a year column in your ddate table and filter this column on 2021. Otherwise Power BI does not know what the max date is in the formula. Please see below.

When I filter on 2021 and 2022:

when I put the filter on 2021.

so even if you only have 2021 data please add a year column in the data table and put the filter on 2021. can also be done in page filter in the visual pane.

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

[Anonymous]

Try the following:

LastNotBlank =
VAR _date =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        LASTNONBLANK (
            Sales[Value];
            Sales[Value]
        );
        FILTER ( ALLEXCEPT ( 'Date', 'Date'[Date] ); 'Date'[Date] <= _date )
    )

[polestar_11]

@Anonymous Thank you for the answer.

It gives me the following Warning: 

For the Date column, it is not possible to obtain a single value in the Date table. This may happen when a measurement formula references a column with many values without specifying an aggregation such as MIN, MAX, COUNT, or SUM to obtain a single result.