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 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:

 

 

 

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.

     

     

     

     

     

5 Replies

  • Mikelytics's avatar
    Mikelytics
    Icon for Resident Rockstar rankResident Rockstar
    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.

     

    • polestar_11's avatar
      polestar_11
      Frequent Visitor
      3 years ago

      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's avatar
        Mikelytics
        Icon for Resident Rockstar rankResident Rockstar
        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.