Forum Discussion

polestar_11's avatar
polestar_11
Frequent Visitor
3 years ago
Solved

Lastnonblank / Fill Down with DAX Measure

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 ...
Need Help
  • Mikelytics's avatar
    Mikelytics
    3 years ago

    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's avatar
    Mikelytics
    3 years ago

    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's avatar
Anonymous
Not applicable
3 years ago

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's avatar
polestar_11
Frequent Visitor
3 years ago

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.