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:

GeneralLogic

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 Smile

In Excel we would simply add a calculation like this and propagate it down to all rows:
ExcelFormula
(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:
FinalPivot

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

Quite handy, isn’t it?

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

32 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. Pingback: DAX – Recursive Calculation | MS Excel | Power Pivot | DAX

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

        • 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

  4. Pingback: Product of Previous Value

  5. Pingback: Conditional Recursive Calculation [NEED HELP]

    • 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

  6. Pingback: Custom Calculated Measure

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

  8. Pingback: Avoid circular conflict

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

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

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

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

Leave a Reply