How to create calculated column which contain a value = current value *0.3 + previous value *0.7

[kidpk111]

Here is how I wanted my table to be, but I still can't figure out how to do it. The idea in my mind is to create a cumulative formula for this but I don't know how. Anyone have experience on this ?

[tamerj1 · Accepted solution]

Hi @kidpk111 
Attached sample file with the solution

Standarized Value = 
VAR CurrentTime = Data[Time]
VAR CurrentIDTable = CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[ID] ) )
VAR TableOnAndBefore = FILTER ( CurrentIDTable, Data[Time] <= CurrentTime )
VAR Count1 = COUNTROWS ( TableOnAndBefore )
VAR MinTime = MINX ( TableOnAndBefore, Data[Time] )
RETURN
    SUMX ( 
        TableOnAndBefore,
        VAR CurrentValue = Data[Test Value]
        VAR ThisTime = Data[Time]
        VAR T1 = FILTER ( TableOnAndBefore, Data[Time] <= ThisTime )
        VAR Count2 = COUNTROWS ( T1 )
        VAR P1 = IF ( ThisTime = MinTime, 0, 1 )
        VAR P2 = Count1 - Count2
        RETURN
            0.3 ^ P1 * 0.7 ^ P2 * CurrentValue
    )

[tamerj1 · Accepted solution]

Hi @kidpk111 
Attached sample file with the solution

Standarized Value = 
VAR CurrentTime = Data[Time]
VAR CurrentIDTable = CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[ID] ) )
VAR TableOnAndBefore = FILTER ( CurrentIDTable, Data[Time] <= CurrentTime )
VAR Count1 = COUNTROWS ( TableOnAndBefore )
VAR MinTime = MINX ( TableOnAndBefore, Data[Time] )
RETURN
    SUMX ( 
        TableOnAndBefore,
        VAR CurrentValue = Data[Test Value]
        VAR ThisTime = Data[Time]
        VAR T1 = FILTER ( TableOnAndBefore, Data[Time] <= ThisTime )
        VAR Count2 = COUNTROWS ( T1 )
        VAR P1 = IF ( ThisTime = MinTime, 0, 1 )
        VAR P2 = Count1 - Count2
        RETURN
            0.3 ^ P1 * 0.7 ^ P2 * CurrentValue
    )

[kidpk111]

Thank you alot for this, it work like a charm. This is a new technique for me, may I ask why you put VAR inside a formula ?

[tamerj1]

@kidpk111 

Formulas are easier to author and easier to understand using variables. Also this way I can make sure tables and expressions are not calculated multiple times hence the overall formula will be more efficient. 
this is a recursive calculation problem which is not supported by dax therefore, a work around shall require some mathematical skills to be applied in conjunction with your understanding of what dax can and cannot do. 

[kidpk111]

Thank you for your reply, what I don't understand is the logic behind this piece of the formula. Really appreciate your solution because it took me a lot of braincell and still can't figure it out,lol  

[tamerj1 · Accepted solution]

@kidpk111 
I admit it is a bit complex and even more complex to explain. Please refer to attached screenshots that should provide an introduction of this method

[kidpk111]

Thank a lot for your time. I'm really appreciate it