How to perform sum of previous cells of same column in PowerBI

1
votes

I am trying to replicate Excel formula to PowerBI.Which is enter image description here

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

1
excel-formulasumdaxpowerbi-desktop
The question not clear. To increase the chance of receiving high quality answers, I would suggest you to edit your question to match the template: 1) Context, 2) Problem, 3) Simple but representative example, 4) Expected solution based on example provided in Section (3). – Seymour

1 Answers

0
votes

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
    )