# Recursive Calculations in PowerPivot using DAX

If you have ever tried to implement a recursive calculations in DAX similar to how you would have done it back in the good old days of MDX (see here) you would probably have come up with a DAX formula similar to the one below:

1. Sales ForeCast :=
2. IF (
3.     NOT ( ISBLANK ( [Sales] ) ),
4.     [Sales],
5.     CALCULATE (
6.         [Sales ForeCast],
7.         DATEADD ( 'Date'[Calendar], 1, MONTH )
8.     ) * 1.05
9. )

However, in DAX you would end up with the following error:

 A circular dependency was detected: ‘Sales'[Sales ForeCast],’Sales'[Sales ForeCast].

This makes sense as you cannot reference a variable within its own definition – e.g. X = X + 1 cannot be defined from a mathematical point of view (at least according to my limited math skills). MDX is somehow special here where the SSAS engine takes care of this recursion by taking the IF() into account.

So where could you possible need a recursive calculation like this? In my example I will do some very basic forecasting based on monthly growth rates. I have a table with my actual sales and another table for my expected monthly growth as percentages. If I do not have any actual sales I want to use my expected monthly growth to calculate my forecast starting with my last actual sales:

This is a very common requirement for finance applications, its is very easy to achieve in pure Excel but very though to do in DAX as you probably realized on your own what finally led you here

In Excel we would simply add a calculation like this and propagate it down to all rows:

(assuming column C contains your Sales, D your Planned Growth Rate and M is the column where the formula itself resides)

In order to solve this in DAX we have to completely rewrite our calculation! The general approach that we are going to use was already explained by Mosha Pasumansky some years back, but for MDX. So I adopted the logic and changed it accordingly to also work with DAX. I split down the solution into several steps:
1) find the last actual sales – April 2015 with a value of 35
2) find out with which value we have to multiply our previous months value to get the current month’s Forecast
3) calculate the natural logarithm (DAX LN()-function) of the value in step 2)
4) Sum all values from the beginning of time until the current month
5) Raise our sum-value from step 4) to the power of [e] using DAX EXP()-function
6) do some cosmetic and display our new value if no actual sales exist and take care of aggregation into higher levels

Note: The new Office 2016 Preview introduces a couple of new DAX functions, including PRODUCTX() which can be used to combine the Steps 3) to 5) into one simple formula without using any complex LN() and EXP() combinations.

Step 1:
We can use this formula to get our last sales:

1. Last Sales :=
2. IF (
3.     ISBLANK (
4.         CALCULATE (
5.             [Sales],
6.             DATEADD ( 'Date'[DateValue], 1, MONTH )
7.         )
8.     ),
9.     [Sales],
10.     1
11. )

It basically checks if there are no [Sales] next month. If yes, we use the current [Sales]-value as our [Last Sales], otherwise we use a fixed value of 1 as a multiplication with 1 has no impact on the final result.

Step 2:
Get our multiplier for each month:

1. MultiplyBy :=
2. IF (
3.     ISBLANK ( [Last Sales] ),
4.     1 + [Planned GrowthRate],
5.     [Last Sales]
6. )

If we do not have any [Last Sales], we use our [Planned GrowthRate] to for our later multiplication/summation, otherwise take our [Last Sales]-value.

Step 3 and 4:
As we cannot use “Multiply” as our aggregation we first need to calculate the LN and sum it up from the first month to the current month:

1. Cumulated LN :=
2. CALCULATE (
3.     SUMX ( VALUES ( 'Date'[Month] ), LN ( [MultiplyBy] ) ),
4.     DATESBETWEEN (
5.         'Date'[DateValue],
6.         BLANK (),
7.         MAX ( 'Date'[DateValue] )
8.     )
9. )

Step 5 and 6:
If there are no actual sales, we display our calculated Forecast:

1. Sales ForeCast :=
2. SUMX (
3.     VALUES ( 'Date'[Month] ),
4.     IF ( ISBLANK ( [Sales] ), EXP ( [Cumulated LN] ), [Sales] )
5. )

Note that we need to use SUMX over our Months here in order to also get correct subtotals on higher levels, e.g. Years. That’s all the SUMX is necessary for, the IF itself should be self-explaining here.

So here is the final result – check out the last column:

The calculation is flexible enough to handle missing sales. So if for example we would only have sales for January, our recursion would start there and use the [Planned GrowthRate] already to calculate the February Forecast-value:

Quite handy, isn’t it?

## 55 Replies to “Recursive Calculations in PowerPivot using DAX”

1. Superb, Gerhard!!! Yet I’m not sure that I completely understand LN and EXP functions because I’m not so familiar with math. 🙂
Though it can be excellently used as a pattern.

• The goal is to turn a sum into a product using 2 log properties:
1: ln(a) + ln(b) = ln(a.b)
2: e^(ln(x)) = x
So: e^(ln(a) + ln(b) + ln(c) + …) = a.b.c.(…)
Very smart use, thanks for the solution.

2. Nice work!

But is there any possibility to extend this concept with daily,monthly or yearly deposits?

Like x(n) = x(n-1)*r+c(n) , where (n),(n-1) are indexes.

Thanks for suggestions.

• Hi Oscar,
by daily/monthly/yearly deposits you mean that you are able to add/subtract an absolute value at a given time-period and incorporate this change into the calculation?
I have not thought of this yet but my first impression is that this is not possible – at least not with this approach as we are only dealing with relative values/percentages here

sorry,
-gerhard

• Hello Gerhard,

Have you found any way to incorporate deposits?

• Hi Fabio,

unfortunately no, this gets a complicated mathematical problem to find the right multipliers to be used and I have not had any time yet to dig into it and I am still not sure if it is possible at all.

sorry,
-gerhard

3. Pingback: Product of Previous Value

4. Hi , can you show ho to use your PRODUCTX() in your example ?

• the calculation should look like this:
Sales ForeCast PRODUCTX:=IF(ISBLANK([Sales]),CALCULATE(
PRODUCTX(
VALUES(‘Date'[Month]),
[MultiplyBy]),
DATESBETWEEN(‘Date'[DateValue], BLANK(), MAX(‘Date'[DateValue]))
),
[Sales]
)

I also updated the sample .xlsx-file to include the new calculation

5. Pingback: Custom Calculated Measure

6. God bless you… I’ve been looking for hours on how to do this – I still dont totall understand it, but the combo Calculate(sumx(values())) did what I wanted, which is to sum non-linear calculations… THANK YOU!

7. Pingback: Avoid circular conflict

8. Hi Gerhard,

thanks for this great post!

I have a slightly different issue. I don’t have dates in my rows in the pivot but numbers (tenures). They are not exactly a rank as they have gaps. Unfortunately I can’t even get step one of your article working. I want to have the the number of customers from the tenure before for further calculations. To make things simple:

CALCULATE (
COUTROWS[Customer],
FILTER(Customer,Customer[Tenure]-1) )

However this gives me the number of customers from the current row of the pivot table and not the number from the previous row. In my raw data I have the the name of each customer with the corresponding tenure.

Does your approach only work with dates? I have trying to get my head around this for hours this but it doesn’t work out.

Any help is highly appreciated!

Thanks!

Stefan

• Hi Stefan,

The “-1” is used to refer to the previous day. If you do not have dates, you need to find another way to reference the previous row. If you have gaps in your values, you might try to create a calculated column using RANKX() to get a list of subsequent number to be used in the formula (depending on your data of course)

-gerhard

• Hey Gerhard,

That is what I thought too. As I am only able to use the 2010 version I don’t have RANKX() available. I used SQL instead to solve this, so every row in my power pivot table has a rank. Of course there a lot of rows that have the same rank as they have the same tenure.

However if I use

CALCULATE (
COUTROWS[Customer],
FILTER(Customer,Customer[Rank]-1) )

still the previous row is not adressed. It still shows the value of the current row apart from the first row, that is empty. I don’t get it 🙁

Row Label Count Previous Count
1 4366
2 120 120
3 82 82
4 154 154
5 97 97
6 111 111
7 195 195
8 100 100
9 85 85
10 91 91
11 104 104
12 109 109
13 177 177
14 102 102
15 73 73

Thanks,

Stefan

• Hi Stefan,
can you try this calculation:
CALCULATE (
COUTROWS[Customer],
FILTER(
CALCULATETABLE(
Customer,
ALL(Customer[Rank])),
Customer[Rank] = MAX(Customer[Rank]) – 1)
)

regards,
-gerhard

• Hi Gerhard,

And the best thing is: It works! 🙂

Can you explain your solution to me? I was working on a solution for hours and hours but did not even come close to it. What was wrong in my thinking? I guess it has to do with CALCULATETABLE?

Another question now would be the row labels. The measure only works if I have rank in the row labels. If I use tenure, it is blank. However it would be necessary to have the count of customers from the previous tenure. The rank is just a proxy to do the calculation.

Thanks a lot again!

Stefan

• ok, let me first explain your original calculation, especially the FILTER-part
CALCULATE (COUTROWS[Customer], FILTER(Customer,Customer[Rank]-1) )
the second argument has to return a boolean value (true or false). so by using Customer[Rank] – 1 all rows, except for Rank=1 return something different than 0/false thats why the first row was blank and the others were populated. But there is also an other issue. Referencing ‘Customer’ only returns the rows that are active in the current row – e.g. for the 3rd row, all rows where Rank=3. If you do not remove this filter but add another filter like Rank=2 you would get 0 rows returned. Hence you need to remove the Filter from Customer[Rank] which i did in my CALCULATETABLE.

to also make it work with [Tenure], you would also need to add ALL(‘Customer'[Tenure]) to the CALCULATETABLE function

hope this explains it a bit better

9. Hi Gerard,

Thanks for this post it is really useful.

However if I understand it well it is not a real recursion as it calculates directly from the last actual value. I am wondering how could I do the similar but not with percentages but with other calculation like:
[Planned Backlog] n = [Planned OIT] period n + (Planned Backlog period n-1 – Planned Revenue period n-1)

This is the same issue which I would solve it in excel by referencing back to the previously calculated cell.

Tom

• yes, I think this would also be possible, but it would probably need some additional intermediate measures to calculate the previous-previous-months value, rest should be quite similar but I have not tested it yet

-gerhard

Previous-previous-month?? Just to be absolute clear……What I want is to calculate forecast using previons-month sales and previous month.

• with previous-previous-month I wanted to refer to sales made 2 months ago 🙂

• Ahhhh, now I see why you are getting confused. Wrong formular in my example so it should be easy to understand now. This is now updated if you use the link.

Basically I need to calculate this month forecast based on previous month’s sales and previous month forecast.

Hope you can send me in the right direction:-)

• The query solution can work on the short run, but I am wondering if an DAX-solution will be more solid on the long run. know. Anyway, now I have used some time on this issues so it would absolutely be satisfying so find out how a solution could like using DAX.

• By the way I really appreciate the time you use on this issue…………..

• ok, just did a quick test and the problem seems to be more complex than I initially thought.
My solution is based on finding a multiplicator which is then applied using EXP/LN/SUM/MULTIPLY logics. This calculation itself would also work for your scenario but finding the right multiplicators is far more complex and more of a mathematical problem than an actual DAX problem.
I am afraid I cannot help you too much on this, sorry
-gerhard

• No problem at all, thanks for take your time to give it a shot:-) By the way….I will follow your blog and will be looking forward to this.

10. Hi,

I am not sure if you answered this, but is it possible to use a different method of calculation instead of the LN for regular numbers and not multiply?
If I have:
Ending Inventory was known for a period time than it should turn into :
Ending Inventory= Beginning Inventory -Monthly Forecast +batch size

Can you help with that?

• sure, the SUM / LN is only necessary because PRODUCTX-function was not available at the time of writing this post
but the calculation you want to solve has nothing to do with recursion, it is basically just a simple addition/subtraction of various numbers. The difficulty lies in calculating the single values first in order to be able to get your final value.

-gerhard

• I am glad to hear it’s a simple solution! maybe you can help me with the magic formula for that.
My last data point for Ending Inventory table is June 2017, I was able to calculate the opening Inventory for July 2017 using:
1) Future Beginning Inventory:=if(ISBLANK(CALCULATE([Ending Inventory(Units)],DATEADD(‘Date'[Date],1,month))),[Ending Inventory (Units)],1)

2)This Formula was correct for July 2017 but after that it had only Batch size- forecast. Future ending Inventory:=if(ISBLANK([Future Beginning Inventory]),[Updated Batch Size]-[Forecast Units],[Future Beginning Inventory])

any suggestions??

I dont know how [Batch Size], [Monthly Forecast] or even your inventory measures are defined

• 3 different tables:
*Batch size is defined by material ID number.
*Monthly forecast is dates starting at 7/1/2017, 8/1/2017 etc , Material ID and Forecast units.
*Ending Inventory: Material ID, Dates (by days) , and Ending Inventory Units. (the table ends on 6/1/2017)

The model itself has other tables, I would be happy to send a sample of it but not sure how to attach a file here..

• I was asked a similar question by FABIANO JUNIOR JOHANN and I am afraid a moving average is much more difficult to solve than the example in the blog post. However, the approach would be quite similar – find the last 6 months with sales and, depending on the month which you want to forecast, you need to find a factor for each of the 6 month sales values which defines how much it contributes to the forecast. This is more like a mathematical problem than a DAX problem and I am afraid I cannot really help you here :/

-gerhard

11. Gerard,

Thanks for the post, helped a ton. I using this to calculate index series for financial data (as the basis for volatility and other financial metrics). The approach works but becomes really slow once you have a few years of daily data. Have you found any alternatives to the above till using DAX?

The only other option I have found (given a need to use Power BI) is to create custom visual and use either R or Typescript. Both with their own disadvantages.

Peter Björkmarker

• Well, the main reason to do it in DAX is that it is dynamic in terms of filters – this very much depends on the use case of course and if this applies to your scenario.
if you have a static forecast without any changing parameters, you could probably also do some or most of the calculation already in PowerQuery or in a R DataSoruce. This way you could work around slow on-the-fly calculations in DAX.

regards,
-gerhard

12. Gerhard, can you explain how to extend this to Year-over-year? For example, I want to forecast different months to have different increases in sales YoY. So, instead of referencing the previous month, I want to reference the same month from the previous year. I’m having trouble getting the table filtering down…

• I think you can just change the [Last Sales] measure to reference the previous year instead of the previous month by changing. Basically the measure returns 1 for every row except for the the last month where sales exists where it returns the sales. This is the basis for the iteration.

If you want to reference the same month in the previous year, you need to return CALCULATE([Sales], DATEADD ( ‘Date'[DateValue], -1, YEAR) in the second parameter of the IF() instead of [Sales] only

does this work for you?

-gerhard

• Thanks for the quick response, Gerhard.

I’m ultimately trying to forecast by day (using a month-level growth factor) and I already made your suggested update to the [Last Sales] measure. I think the issue is actually in the final [Sales ForeCast PRODUCTX] measure.

My understanding of this measure is that for each row in the PivotTable, it’s calculating the product of all the previous values found in [MultiplyBy]. I’m having trouble filtering in this measure to have it only take the product of the equivalent dates (accounting for day of week) across years instead of all dates. For example:
3/8/16
3/7/17
3/6/18

So, I think I need to adjust the table expression in the PRODUCTX() statement of the [Sales ForeCast PRODUCTX] measure to filter for that pattern of dates, but I’m having trouble getting that to work.

Does that make sense?

• ok, so if you want to do your calculation on a daily level, how does this work together with a monthly forecast rate?
at some point we need to get the last sales from which you want to start your forecast – this is [LastSales]
Then, depending your scenario, the idea is to multiply this [LastSales] with the growth rate (=[MultiplyBy]). Of course it matters on which level of granularity you do this calculation. if you do it on a monthly level with a monthly forecast rate, then the table expression in PRODUCTX() has to return a table on the monthly level.
So I dont know how a forecast on a daily level with a monthly forecast rate could work? do you want to multiply each day with the monthly rate?

if you have a sample file to share I would be happy to take a look at it

• Hey Gerhard,

You’re correct, I’m applying the monthly forecast rate to the dates within the month.

I took your sample file and updated to YEAR instead of MONTH for [Last Sales] and sent it along to your email.

Thanks again!

• How can I calculate the interest on the sales, add to the next month and then calculate the interest, but from the sum of the sale with the interest of the previous month.
Notes: Interest is calculated only if the sale is positive, if it is not 0.

I attach the data that I have as a basis and what I hope to obtain.
Base data:
Period Sales
Ene 100
Feb -40
Mar -50
Abr -25
May -10
Jun 100
Jul 10
Ago 25
Sep 30
Oct 30
Nov -50
Dic 20

Result:
Cumulative sale
100
60
10
-15
-25
75
85
110
140
170
120
140
Total+1 Month Interest= (total*(0.05/12)) +1Month
0 0
100 0.4166
60.4166 0.2517
10.2517 0.0427
-14.7056 0
-24.7056 0
75.2944 0.3137
80.6081 0.3567
105.9648 0.4415
136.4063 0.5683
166.9746 0.6957
117.6703 0.4902

Sales Total= Total+1 Month + Interest + Sale
100 100
-40 60.4166
-50 10.2517
-25 -14.7056
-10 -24.7056
100 75.2944
10 80.6081
25 105.9648
30 136.4063
30 166.9746
-50 117.6703
20 138.1605

13. Thanks For Your Help! This article was well written and really helped me build out what I needed.

14. Hey Gerhard,

How can I calculate the interest on the sales, add to the next month and then calculate the interest, but from the sum of the sale with the interest of the previous month.
Notes:
The interest is calculated only if the sale is positive,if it is not 0.

Best regards

15. Hey Gerhard,

How can I calculate the interest on the sales, add to the next month and then calculate the interest, but from the sum of the sale with the interest of the previous month. Notes: Interest is calculated only if the sale is positive, if it is not 0. I attached an image with the effect I hope to obtain. Best regards

16. Hi Gerhard, thanks for this critically important post. Would it be possible to recreate your exact model except using PRODUCTX? I’ve been pulling my hair out for quite a while on this but just can’t find the right DAX.

• Hi,

(simply search for “PRODUCTX” in the comments)

regards,
-gerhard

17. Hi Gerhard,

Thanks for a great and insightful post. However, I still have a circular dependency that I cannot resolve.

I have been trying all sort of tricks with DAX, but just cannot get to a solution to my challenge. Would appreciate some help.

I have quite a practical example where I need to build an inventory planning model. Everything is working, except the aspect where I have to refer to my previous days stock on hand to calculate today’s stock on hand. This was easy in Excel, but I cannot replicate in Power BI.

In Excel, this was quite easy. But when I implement in Power BI I create Circular dependencies. In Excel, I have my sales column that is calculated from actual sales. I have my SOH column (beginning SOH) that refers to the previous day’s (SOH+OPEN PO’s) Column. I have my (SOH+OPEN PO’s) column that calculates from SOH- Sales + PO Placed. Lastly, I have my PO Place column that refers to my (SOH+OPEN PO’s) column to calculate if a PO must get generated or not. The problem is the dependence of my PO Placed and SOH + OPEN PO’s.

The trick comes in that I calculate my PO Placed from my SOH column in Excel and that creates the circular dependency in Power BI.

Please see my post on the following link as I cannot share images here:

https://community.powerbi.com/t5/Desktop/Reference-the-previous-row-s-value-in-the-same-column-of-a/m-p/844758#M405613