Hello! i need to create a complex calculated column. Here is the sample data:
| Index | Step | Value |
| 0 | 0 | 5 |
| 1 | 3 | 7 |
| 2 | 6 | 6 |
| 3 | 9 | 8 |
| 4 | 12 | 7 |
The new column must follow this logic:
Column(0) = Value(0)
Column (1) = Value(1) + 10 · Value(0) · (Step(0) - Step(1))
Column (row = 2) = Value(2) + 10 · Value(1) · (Step(1) - Step(2)) + 10^2 · Value(0) · (Step(1) - Step(2)) · (Step(0) - Step(1))
Column (row = 3) = Value(3) + 10 · Value(2) · (Step(2) - Step(3)) + 10^2 · Value(1) · (Step(2) - Step(3)) · (Step(1) - Step(2)) + 10^3 · Value(0) · (Step(2) - Step(3)) · (Step(1) - Step(2)) · (Step(0) - Step(1))
...
I tried this DAX expression, but I cannot get the current iteration the SUMX.
VAR __Ind = Data[Index]
RETURN
CALCULATE(
SUMX(
FILTER( Data, Data[Index] >= __Ind - 2 && Data[Index] <= __Ind ),
VAR __i = Data[Index]
RETURN
PRODUCTX (
FILTER ( ALL(Data), [Index] <= __i && [Index] > 0),
Data[PreviousTimeStamp] - Data[TimeStamp] // Replacing __i with [Index] to store the actual iteration of SUMX
)
)
)
