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
        )
    )
Stachu's avatar
Stachu
Community Champion
8 years ago

if the goal is to have the table filled in you can achieve that in Power Query with few clicks

  • 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