Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Fill blanks with previous value

Hi All,   I have the current table below, which as you can see has missing values.   Car Month Value Fiat Mar-08 10,000 Fiat Apr-08   Fiat May-08   Fiat Jun-08 12,000 ...
Need Help
  • Stachu's avatar
    Stachu
    8 years ago

    what do you mean by 'outside of Power Query'? is it calculated table?
    in DAX you can try this, it works as long as Month column has type date

    Value2 =
VAR CurrentCar = 'Table'[Car]
VAR CurrentDate = 'Table'[Month]
VAR LastDateWithValue =
    CALCULATE (
        MAX ( 'Table'[Month] ),
        FILTER (
            'Table',
            'Table'[Value] <> BLANK ()
                && 'Table'[Car] = CurrentCar
                && 'Table'[Month] <= CurrentDate
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            'Table',
            'Table'[Car] = CurrentCar
                && 'Table'[Month] = LastDateWithValue
        )
    )
Anonymous's avatar
Anonymous
Not applicable
8 years ago

Unfortunately the table is outside of power query, is there a solution in DAX?

Stachu's avatar
Stachu
Community Champion
8 years ago

what do you mean by 'outside of Power Query'? is it calculated table?
in DAX you can try this, it works as long as Month column has type date

Value2 =
VAR CurrentCar = 'Table'[Car]
VAR CurrentDate = 'Table'[Month]
VAR LastDateWithValue =
    CALCULATE (
        MAX ( 'Table'[Month] ),
        FILTER (
            'Table',
            'Table'[Value] <> BLANK ()
                && 'Table'[Car] = CurrentCar
                && 'Table'[Month] <= CurrentDate
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            'Table',
            'Table'[Car] = CurrentCar
                && 'Table'[Month] = LastDateWithValue
        )
    )
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    I used your query and to me, it fills the previous value to blank rows but add some garbage value to existing row value

  • TaufikMaggangka's avatar
    TaufikMaggangka
    Helper II
    4 years ago

    Hi Stachu ,
    I have same case, but the data type of column that need to fill is "Text? type. I try to using your dax but got an erro because SUM dax can used for data type of text.

    basically I have column like this, I need to fill the "status" based on previous date with same school ID.

    Any help will be really appreciate.

    School IDDateStatus
    11 nov 2021High
    21 nov 2021Medium 
    31 nov 2021Low
    12 nov 2021High
    22 nov 2021Low
    32 nov 2021 
       
       
    • InsHunter's avatar
      InsHunter
      Helper II
      3 years ago

      Hi,
      I have a similar situation for me with forward filling of text based on conditions. The logic with SUM works well for numeric. Your solution will greatly help me. Thanks in Advance.

      Regards.

  • lucasneedhelp's avatar
    lucasneedhelp
    Helper I
    3 years ago

    Hi Stachu 
    I tried your code, but it says a single value for column 'car' in table cannot be determined. could you please help