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