How To Sum Previous Cells Of Same Column In PowerBI (Exponential Smoothing)

Question

I am trying to replicate Excel formula to PowerBI.Which is

Is There any DAX to perform this calculation((1-0.2)*B2+0.2*C2). Thanks.

Answer 1

There no inherent way to do relative row reference in DAX, so you need to explicitly tell it which row to reference.

CalculatedColumn =
VAR PrevDate =
    MAXX (
        FILTER ( Table1, Table1[Date] < EARLIER ( Table1[Date] ) ),
        Table1[Date]
    )
VAR B = LOOKUPVALUE ( Table1[B], Table1[Date], PrevDate )
VAR C = LOOKUPVALUE ( Table1[C], Table1[Date], PrevDate )
RETURN
    ( 1 - 0.2 ) * B + 0.2 * C

---

Edit:

Since you've clarified that you're looking to reference the same column that you are defining, the only way I know how to do this is to create a closed-form formula to use, as in my answer here.

With the recurrence relation

C_(n+1) = 0.8 * B_n + 0.2 * C_n

we can rewrite this in terms of C_1 as follows:

C_n = 0.8 * ( sum_(i=1)^(n-1) ( B_i * 0.2^(n-i-1) ) ) + 0.2^(n-1) * C_1

Here, the entire C column is only dependent on column B and a single initial value C_1 = 8320, which is the first term in the B column.

Now we can turn this into a calculated column with a little DAX Magic:

ColumnC = 
VAR C1 = MAXX ( TOPN ( 1, TableN, TableN[Date], ASC ), [B] )
VAR N = RANK.EQ ( [Date], TableN[Date], ASC )
VAR SumTable =
    ADDCOLUMNS (
        FILTER (
            SELECTCOLUMNS (
                TableN,
                "i", RANK.EQ ( [Date], TableN[Date], ASC ),
                "B_i", [B]
            ),
            [i] <= N - 1
        ),
        "B_i Term", POWER ( 0.2, N - [i] - 1 ) * [B_i]
    )
RETURN
    IF (
        N > 1,
        0.8 * SUMX ( SumTable, [B_i Term] ) + POWER ( 0.2, N - 1 ) * C1,
        0
    )

Answer 2

Looks like you are doing exponential smoothing. We can actually generalize the weight of each data point (W_t) as:

W_t = 1 - ( 1 - α ) ^ t

https://www.itl.nist.gov/div898/handbook/pmc/section4/pmc431.htm

Ordinarily, an exponential smoothing expression has a total term weight of 1 because one term is weighted as α, and the other as 1-α. However, because the closed form is an exponential function, any subset of data points will also form an exponential curve. By adjusting the weights proportionately to sum to one, we still maintain the exponential shape. Additionally, identifying the base period is irrelevant so long as all data points use the same base period because we are already correcting for a non-unity denominator.

DIVIDE( SUMX( dates, W_t * X_t ), SUM( W_t ) )

Therefore, you can implement like so:

VAR alpha = 0.2 
VAR AsOfDate = LastDate('Calendar'[Date])
RETURN
CALCULATE(
    VAR summary = 
        SUMMARIZECOLUMNS(
            'Calendar'[Date],
            "weight", 1 - ( 1 - alpha ) ^ ( ( 'Calendar'[Date] - TODAY() ) / 7 ),
            "value",[Value_Measure]
        )
    RETURN
        DIVIDE(
            SUMX( summary, [weight] * [value] ),
            SUMX( summary, [weight] )
        ),
    DATESBETWEEN( 'Calendar'[Date], BLANK(), AsOfDate )
)

This first creates a table with the weights and values for each date (so the weights don't have to be evaluated twice, once for the numerator, and then for the denominator), and then it performs a weighted average of the selected values on or prior to the context date.