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.

 

CarMonthValue
FiatMar-0810,000
FiatApr-08 
FiatMay-08 
FiatJun-0812,000
FiatJul-08 
FiatAug-08 
AudiMar-0822,000
AudiApr-08 
AudiMay-0824,000
AudiJun-08 
AudiJul-0823,000
AudiAug-08 

 

I need to create a calculated column which fills in those blanks based on the last month for that type of car, so something like this:

 

CarMonthValueValue 2
FiatMar-0810,00010,000
FiatApr-08 10,000
FiatMay-08 10,000
FiatJun-0812,00012,000
FiatJul-08 12,000
FiatAug-08 12,000
AudiMar-0822,00022,000
AudiApr-08 22,000
AudiMay-0824,00024,000
AudiJun-08 24,000
AudiJul-0823,00023,000
AudiAug-08 23,000

 

Does anyone have any ideas how to do this? The issue of offsetting is where there are more than two blank rows in sequence.

 

Thanks,

 

Jack

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

7 Replies

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