Forum Discussion

pacoduabe's avatar
pacoduabe
Regular Visitor
3 years ago

Complex DAX Expression PRODUCTX nested in SUMX

Hello! i need to create a complex calculated column. Here is the sample data:

IndexStepValue
005
137
266
398
4127

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
                )
        )
    )
Need Help