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:

- Sales ForeCast :=
- IF (
- NOT ( ISBLANK ( [Sales] ) ),
- [Sales],
- CALCULATE (
- [Sales ForeCast],
- DATEADD ( 'Date'[Calendar], –1, MONTH )
- ) * 1.05
- )

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:

- Last Sales :=
- IF (
- ISBLANK (
- CALCULATE (
- [Sales],
- DATEADD ( 'Date'[DateValue], 1, MONTH )
- )
- ),
- [Sales],
- 1
- )

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:

- MultiplyBy :=
- IF (
- ISBLANK ( [Last Sales] ),
- 1 + [Planned GrowthRate],
- [Last Sales]
- )

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:

- Cumulated LN :=
- CALCULATE (
- SUMX ( VALUES ( 'Date'[Month] ), LN ( [MultiplyBy] ) ),
- DATESBETWEEN (
- 'Date'[DateValue],
- BLANK (),
- MAX ( 'Date'[DateValue] )
- )
- )

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

- Sales ForeCast :=
- SUMX (
- VALUES ( 'Date'[Month] ),
- IF ( ISBLANK ( [Sales] ), EXP ( [Cumulated LN] ), [Sales] )
- )

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?

The sample-workbook (Excel 365) can be downloaded here: RecursiveCalculations.xlsx

Nice, very nice, congrats! Thanks for sharing!

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.

Pingback: DAX – Recursive Calculation | MS Excel | Power Pivot | DAX

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

Pingback: Product of Previous Value

Pingback: Conditional Recursive Calculation [NEED HELP]

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

Pingback: Custom Calculated Measure

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!

Pingback: Avoid circular conflict

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,

thank you very much for your reply!

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,

thank you so much for your super quick reply!

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

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.

Thanks in advance for your answer,

Tom

Hi Gerhard, I have tried to use your methodology to fix the following:

https://community.powerbi.com/t5/Desktop/Recursive-calculation-forecast/m-p/222109#M98652

I was think if you have an idea how to solve this?

/ Søren

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

Thanks for quick reply…..

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:-)

It seems that your question is already answered in the forum?

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.

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 appreciate your help!!!

thats pretty hard without knowing/having access to the model.

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..

just drop me a mail at gerhard@gbrueckl.at

Hi Gerhard, I am looking for something similar, but I couldn’t apply the same concept… I want to plan ahead based on Moving Average. Check this example in excel: https://oxcrx34285.i.lithium.com/t5/image/serverpage/image-id/60919iB1BA2A1801AAFFAB/

Thank you so much!

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

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

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!

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