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 :
Ritaf1983
Super User
2 years agoHi capko
According to your description, you can add a calculated column with DAX :
test =
VAR CurrentItem = 'table'[Item]
VAR MaxDate =
CALCULATE(
MAX('table'[Date]),
FILTER(
'table',
'table'[Item] = CurrentItem
)
)
RETURN
IF(
ISBLANK('table'[Value]),
CALCULATE(
MAX('table'[Value]),
FILTER(
'table',
'table'[Item] = CurrentItem && 'table'[Date] = MaxDate
)
),
'table'[Value]
)
The results of item 1 are not equal to your screenshot because for item 1 last value is 5
- capko2 years ago
Helper II
Hello,
Thanks for your help !
However there is a problem with this solution because it takes the maximum value if the value cell is empty and I would like to take the last available value (if we order the table for a given item with respect to the date in an ascending way).
I've tried your solution and I filtered the table just for Item 1. I ordered the table then by date and we can see that the values in column 'test' are not the good ones :