Forum Discussion

ForzaMami's avatar
ForzaMami
Regular Visitor
4 years ago

one top row data

It's a little hard to explain, I need to use the same formula in a single dax formula, the one in the top row. I'm trying to power the "Exponential Smoothing" column in the excel below. Is something ...
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi ForzaMami ，

According to the information inside the latest reply you gave, the result of the current item is calculated based on the result after the previous item is calculated. And as tamerj1 said, it involves recursive calculation, I'm afraid it's difficult to achieve what you need with DAX...

Best Regards

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi ForzaMami 
    It is complicated but actually there might be a solution. Following is a silution based on a calculated column. Not sure if this is what you are looking for. https://we.tl/t-k7c9RgpgGw

    Exponential = 
VAR a = 0.5
VAR CurrentDate = Sales[Year_Month]
VAR FirstDateEver = CALCULATE ( MIN ( Sales[Year_Month] ), REMOVEFILTERS ( ) )
VAR FirstAmount = MINX ( FILTER ( Sales, Sales[Year_Month] = FirstDateEver ), Sales[Sales] )
VAR T1 = FILTER ( Sales, Sales[Year_Month] < CurrentDate )
VAR PowerNum1 = COUNTROWS ( T1 ) - 1
VAR Value1 = IF ( PowerNum1 >= 0, FirstAmount * POWER ( a, PowerNum1 ) )
VAR Value2 =
    SUMX (
        T1,
        VAR PowerNum2 = COUNTROWS ( FILTER ( T1, Sales[Year_Month] >= EARLIER ( Sales[Year_Month] ) ) ) - 1
        VAR Amount = MAXX ( FILTER ( T1, Sales[Year_Month] = EARLIER ( Sales[Year_Month], 1 ) + 100 ), Sales[Sales] )
        RETURN
            POWER ( a, PowerNum2 ) * Amount
    )
RETURN
    Value1 + Value2