Recursive calculation with measures

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