Calculate Adstock in DAX: how to add value of cell above?-CodePudding

In Power BI I need to build a so called AdStock measure: This is today's value plus a decayed yesterday's value. Easy and straightforward in Excel like this:

But how can I do this in DAX?

The solution of the first answers unfortunately is not correct if AdSpend decreases somewhen, the result is not correct from Index 4 on:

It should look like this:

CodePudding user response：

Calculated Column:

AdStock :=
VAR AdStockRate = 0.5
VAR ThisIndex = [Index]
RETURN
    [AdSpend]
          SUMX (
            FILTER ( Table1 , [Index] < ( ThisIndex - [Index] ) ),
            [AdSpend] * AdStockRate ^ [Index]
        )

Amend the table name (Table1 here) as required.

Note that I have hard-coded the AdStockRate (0.5) here, though naturally it could be replaced with a dynamic reference.

Or, as a Measure:

AdStock :=
VAR AdStockRate = 0.5
VAR ThisIndex =
    MIN ( Table1[Index] )
VAR AdSpend =
    MIN ( Table1[AdSpend] )
RETURN
    AdSpend
          SUMX (
            FILTER ( ALL ( Table1 ), Table1[Index] < ThisIndex ),
            Table1[AdSpend] * AdStockRate ^ ( ThisIndex - Table1[Index] )
        )