Fill blanks with previous value

[Anonymous]

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
Fiat	Jul-08
Fiat	Aug-08
Audi	Mar-08	22,000
Audi	Apr-08
Audi	May-08	24,000
Audi	Jun-08
Audi	Jul-08	23,000
Audi	Aug-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:

Car	Month	Value	Value 2
Fiat	Mar-08	10,000	10,000
Fiat	Apr-08		10,000
Fiat	May-08		10,000
Fiat	Jun-08	12,000	12,000
Fiat	Jul-08		12,000
Fiat	Aug-08		12,000
Audi	Mar-08	22,000	22,000
Audi	Apr-08		22,000
Audi	May-08	24,000	24,000
Audi	Jun-08		24,000
Audi	Jul-08	23,000	23,000
Audi	Aug-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

[Stachu]

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

[Anonymous]

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

[Stachu · Accepted solution]

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]

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

[TaufikMaggangka]

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 ID	Date	Status
1	1 nov 2021	High
2	1 nov 2021	Medium
3	1 nov 2021	Low
1	2 nov 2021	High
2	2 nov 2021	Low
3	2 nov 2021

[InsHunter]

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.

[Anonymous]

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