Recursive Calculations in PowerPivot using DAX

Posted on by — 67 Comments ↓

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

67 Replies to “Recursive Calculations in PowerPivot using DAX”

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

    Reply ↓

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

      Reply ↓

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

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

    Reply ↓

    • 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

      Reply ↓

        • 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

          Reply ↓

  5. Pingback: Product of Previous Value

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

      Reply ↓

  8. Pingback: Custom Calculated Measure

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

    Reply ↓

  10. Pingback: Avoid circular conflict

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

    Reply ↓

    • 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

      Reply ↓

      • 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

        Reply ↓

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

          regards,
          -gerhard

          Reply ↓

          • 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

            Reply ↓

            • 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

              Reply ↓

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

    Reply ↓

      • As mentioned also in the post itself, DAX does not support real recursive calculations – the solution shown here is just a workaround for very basic calculations and very simple scenarios.
        For more complex calculations I am afraid DAX is not the right answer here

        Reply ↓

    • 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

      Reply ↓

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

        Reply ↓

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

            Reply ↓

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

                Reply ↓

                • 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

                  Reply ↓

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

                    Reply ↓

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

    Reply ↓

    • 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

      Reply ↓

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

        Reply ↓

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

            Reply ↓

    • 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

      Reply ↓

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

    Reply ↓

    • 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

      Reply ↓

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

    Reply ↓

    • 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

      Reply ↓

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

        Reply ↓

        • 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

          Reply ↓

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

            Reply ↓

          • 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

            Reply ↓

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

    Reply ↓

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

    Reply ↓

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

    Reply ↓

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

    Reply ↓

      • Thanks Gerhard. Yeah, I found it in the comments after I asked and in your download as well. Very helpful. Thanks again for sharing your expertise.

        Reply ↓

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

    Looking forward to your response.

    Reply ↓

  23. Hi Gerhard,

    I have just logged in to thank you for this old yet still 100% relevant post. You have saved me tons of work!

    LEGEND!

    Regards,
    Vas

    Reply ↓

  24. Hi Gerhard
    Thank you for this wonderful technique. Isn’t it wonderful that this post is helping folks 5 years after publishing? Amazing.

    Is there a way to have different growth rates for the different products?

    Thanks again,

    George.

    Reply ↓

    • sure, you could wrap everything inside a SUMX(Product[ProductKey], …)
      similar to what we already do with Date[Month]
      You just need to ensure that each measure used in the calculation returns the right value for each single product

      Reply ↓

  25. Hi,

    I would like to use this script for an entire balance sheet in an automated fashion without having to rewrite the measures for each balance sheet item? (i.e. I would end up with cash forecast, loan forecast, land forecast being automatically generate in the powerpivot.

    Reply ↓

  26. Hi Gerhard,

    What you did is awesome!

    I have a slightly different problem that requires recursive calculation. Do you think it’s possible to achieve the following with DAX?

    Volume=MIN(A,B-C) where C is a cumulative Volume up to last month.

    I need to achieve this using measures, not calculated columns. Is it doable in Power BI?

    Thank you 🙂
    Kasia

    Reply ↓

    • Yes, if you do not use “Volume” again as a starting point for a new recursion, this would work
      if C is based on Volume in a recursive way then I am afraid this is not possible

      Reply ↓

      • Unfortunately, I need to use Volume in a recursive way…
        but at least now I know it’s not possible in DAX…

        Thank you for your prompt reply!

        Reply ↓

Leave a Reply

Your email address will not be published. Required fields are marked *

*