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

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