Forum Discussion
capko
Helper II
2 years agoFilling gaps with last value available
Hello, I've a table like the following one: Item Date Value 1 2021-01 0.5 1 2021-03 1 2021-02 2.3 1 2021-04 2 2021-01 7.2 4 2021-01 ...
- 2 years ago
I think I finally found a solution for my own problem.
Steps to solution:
- Table is filtered at each line using as criteria the item, the date (it must be lower than the one in the actual line) and I remove all the BLANK lines in the value column
- Now I have a table without BLANK values, for the given item and for all the dates lower than the one in the actual line
- I get the max date of this table
- I get the value of this max date using a SUMX applying once again another filter based on the max date found previously
test = VAR CurrentItem = Table[Item] VAR CurrentDate = Table[Date] VAR FilteredTable = FILTER(Table;Table[Item] = CurrentItem && Table[Date] < CurrentDate && Table[Value] <> BLANK()) VAR Last_Max_Date_Value = MAXX(FilteredTable;[Date]) VAR FilteredTable_MaxDate = FILTER(FilteredTable;[Date] = Last_Max_Date_Value) VAR Value_For_Last_Max_Date_Value = SUMX(FilteredTable_MaxDate;[Value]) RETURN IF( ISBLANK(Table[Value]); Value_For_Last_Max_Date_Value; Table[Value] )The final table just for item 1:
The final table with all items :
capko
Helper II
2 years agoI think I finally found a solution for my own problem.
Steps to solution:
- Table is filtered at each line using as criteria the item, the date (it must be lower than the one in the actual line) and I remove all the BLANK lines in the value column
- Now I have a table without BLANK values, for the given item and for all the dates lower than the one in the actual line
- I get the max date of this table
- I get the value of this max date using a SUMX applying once again another filter based on the max date found previously
test =
VAR CurrentItem = Table[Item]
VAR CurrentDate = Table[Date]
VAR FilteredTable = FILTER(Table;Table[Item] = CurrentItem && Table[Date] < CurrentDate && Table[Value] <> BLANK())
VAR Last_Max_Date_Value = MAXX(FilteredTable;[Date])
VAR FilteredTable_MaxDate = FILTER(FilteredTable;[Date] = Last_Max_Date_Value)
VAR Value_For_Last_Max_Date_Value = SUMX(FilteredTable_MaxDate;[Value])
RETURN
IF(
ISBLANK(Table[Value]);
Value_For_Last_Max_Date_Value;
Table[Value]
)
The final table just for item 1:
The final table with all items :