self reference calculation -powerbi dax

Question

I am struggling to make a calculation in powerbi where each row takes the value from previous calculated value

In the image below (cell highlighted), B7 is calculated taking the B6 value and B8 would be calculated taking Freshly calculated B7 value, and so on. I should also mention values until B6 (Month July,2021) are coming from a database column and I am trying to make a forecasting model taking calculated values for each month as a base for coming future months.

Recursion in DAX is not possible in general but sometimes you find workarounds (see Edit 2 in the linked post for examples). — Alexis Olson
Thankyou for mentioning the Linked post. I did read all the posts but how can we create a closed form formula for this.I am still on learning curve of DAX and would appreciate all the help here to solve this. I have been clinging on to this for weeks but didn't make any success yet. — Dipali Amar
I can’t quite tell what your rule is. Are you multiplying the previous row by the same number each subsequent row? — Alexis Olson
Yes that is right. I have a column B and till July,2021 my data is coming from an azure sql database and taking July as my base number , I need to develop a forecasting model for future months. For example in August the forecasted number should be (42563*93%)*97% which comes out closer to 38395. Now for September ,2021 it should take 38395 as the base number and multiple with similar factors i.e. (38395*93%)*97% . These 93 % and 97% are two what-if parameters that have been used in the calculations. — Dipali Amar
Then you just need 42563 * ( 0.93 * 0.97 ) ^ (Row - 6) for rows 7+. — Alexis Olson

Alexis Olson Alexis Olson · Accepted Answer · 2021-08-18T14:34:32

Assuming you have a DB table with data through 7/1/2020, you can write a new calculated table like this:

CalcTable = 
VAR LastDateDB = MAX ( 'DB'[Month] )
VAR LastValueDB = LOOKUPVALUE ( 'DB'[Data], 'DB'[Month], LastDateDB )
RETURN
    ADDCOLUMNS (
        ADDCOLUMNS ( GENERATESERIES ( 3, 10 ), "Month", DATE ( 2021, [Value], 1 ) ),
        "Data",
            IF (
                [Value] <= 7,
                LOOKUPVALUE ( 'DB'[Data], 'DB'[Month], [Month] ),
                LastValueDB * POWER ( 0.93 * 0.985, [Value] - 7 )
            )
    )

self reference calculation -powerbi dax

1 Answers