Forum Discussion
Fill blanks with previous value
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 dateValue2 = 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 ) )
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
)
)
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 | |
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.
