Forum Discussion

kidpk111's avatar
kidpk111
Helper I
3 years ago
Solved

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

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 ? ...
Need Help
Tutorial Requests
  • tamerj1's avatar
    tamerj1
    3 years ago

    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's avatar
    tamerj1
    3 years ago

    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

tamerj1's avatar
tamerj1
Community Champion
3 years ago

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's avatar
kidpk111
Helper I
3 years ago

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's avatar
    tamerj1
    Community Champion
    3 years ago

    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's avatar
      kidpk111
      Helper I
      3 years ago

      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's avatar
        tamerj1
        Community Champion
        3 years ago

        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