Forum Discussion
Bring Last Value Based on Date
Hello folks,
I got myself struggling trying to solve a simple thing i think:
I have this little table here which tells me when a certain value starts to being used.
| Index | Value |
| 1 | 20 |
| 5 | 30 |
| 7 | 40 |
| 9 | 50 |
and the next one are the index table
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
I want a matrix like this one, a parallel in excel would be XLOOKUP with match mode set to -1.
| 1 | 20 |
| 2 | 20 |
| 3 | 20 |
| 4 | 20 |
| 5 | 30 |
| 6 | 30 |
| 7 | 40 |
| 8 | 40 |
| 9 | 50 |
| 10 | 50 |
For your reference.
Step 0: I use these data.
Step 1: I merge these tables.
Step 2: I expand 'Value' column.
Step 3: I do 'Sort Ascending' on 'Index' column.
[Before]
[After]
Step 4: I do 'Fill Down' on 'Value.1' column.
[Before]
[After]
2 Replies
Please try to create below:
ResolvedValue =
VAR CurrentIndex = IndexTable[Index]
RETURN
MAXX(
FILTER(
ValueTable,
ValueTable[Index] <= CurrentIndex
),
ValueTable[Value]
)For your reference.
Step 0: I use these data.
Step 1: I merge these tables.
Step 2: I expand 'Value' column.
Step 3: I do 'Sort Ascending' on 'Index' column.
[Before]
[After]
Step 4: I do 'Fill Down' on 'Value.1' column.
[Before]
[After]
