Recursive calculation with measures

[Anonymous]

I have a problem where I have a recursive measure that has other measures as inputs into it. I have read a lot of solutions that use calculated columns and iteration to get a quasi-recursive calculation, but I don't think that will work in my case because I'm using measures rather than calculated columns. The reason I need measures is because the input to this includes calculations using what-if parameters. I'm trying to determine if there's any way to break down a recursive problem with measures to work in DAX.

Just for some context, here's my scenario. We're building an "equipment capacity" model where we're predicting when we need to order additional equipment in advance. Currently this is done in Excel and we're trying to move it to Power BI. We are smoothing out our prediction using a combination of moving average, and minimums and maximums.

Here's an example of the data I'm working with:

All of the columns are measures. The "Est. Qty" is basically the input measure for this, which is calculated based on a number of adjustable what-if parameters. "4-Period Moving Avg" is an average based on the Est. Quantity for the current and following 3 periods. The next column takes the minimum of the those 2 values. Then the "Smoothed Result" is the recursive part that I'm having trouble with. Here's the pseudocode for that calculation:

Smoothed Result =
  MAX (
     MIN ( Est. Qty, Moving Avg ),
     Smoothed Result for previous period
  )

The base case is just using "Est. Qty" for that first value.

This highlighted column is the only value I can't figure out how to calculate in DAX, as the "Smoothed Result for previous period" is giving me problems. I've got the moving average and the MIN() calculations working correctly in measures. Any thoughts on if there's any workaround to do a recursive calculation like this using measures?

[OwenAuger · Accepted solution]

Hi @Anonymous 

Thankfully it looks like this recursive calculation can be formulated in with measures.

Sample PBIX attached.

Since Smoothed Result is the maximum of its own previous value and the current value of MIN ( Est. Qty, Moving Avg , it can be restated as:

"the maximum value of MIN ( Est. Qty, Moving Avg ) over all Periods up to the current Period".

So to calculate any value of Smoothed Result, we can iterate over all Periods up to the current Period, and find the largest value of MIN ( Est. Qty, Moving Avg ) using MAXX.

The Smoothed Result measure would then look something like this:

Smoothed Result = 
-- Calculate Smoothed Result in a single period only
VAR CurrentPeriod =
    SELECTEDVALUE ( Period[Period] )
RETURN
    IF (
        NOT ISBLANK ( CurrentPeriod ),
        VAR PeriodAndMin =
            CALCULATETABLE (
                ADDCOLUMNS (
                    VALUES ( Period[Period] ),
                    "@Min", [MIN(Est. Qty, Moving Avg)]
                ),
                ALL ( Period ),
                Period[Period] <= CurrentPeriod
            )
        RETURN
            MAXX ( PeriodAndMin, [@Min] )
    )

Note:

• I have created a separate Period table in my sample model (which I would recommend), but you can adjust the references if Period is in the same table as other data, replacing ALL ( Period ) with ALL ( TableName[Period] ), and Period[Period] with TableName[Period].
• I created two "categories" to test that the measure calculates correctly with different filters applied.

Does this work at your end?

Regards,

Owen

[Anonymous]

Thank you @OwenAuger! I was able to get this working. I was so focused on how to implement the problem in code that I hadn't thought of your simplification to the problem statement, which completely eliminated the recursive part. Thank you!

[OwenAuger]

You're welcome 🙂
Yes that's a good point - as DAX is not truly recursive (measures & columns cannot reference themselves directly or indirectly), the best we can do is reformulate in some way.

Another possibility that comes to mind is to use Power Query (or source database) to define this recursively, but only if you are happy to pre-calculate the results.

[tamerj1]

@OwenAuger 

Brilliant!