Forum Discussion

capko's avatar
capko
Icon for Helper II rankHelper II
2 years ago
Solved

Filling 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      ...
  • capko's avatar
    2 years ago

    I think I finally found a solution for my own problem.

     

    Steps to solution:

     

    1. 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
    2. 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
    3. I get the max date of this table
    4. 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 :