Calculate Forecasted Result into Future Using Results from Two Measures

My problem involves three measures, [% Accepted], [% Accepted Prior Month], and [Avg % Change].

If the [% Accepted] is blank, because we don't have data for that month yet, then I want the column to forecast the next month by calculating the [% Accepted Prior Month] + [Avg % Change] = "forecasted % accepted"

I can get it to do this for one month, but I want it to keep going on into the future, for example:

If [% Accepted] for Jan 2023 is blank, then it looks and finds [% Accepted Prior Month] (10.3%) + [Avg % Change] (1.23%) = 11.53% and puts that value in for Jan 2023

THEN for Feb 2023, it will use the 11.53% as its [% Accepted Prior Month] and add the [Avg % Change] for Feb 2023 to that and keep going month after month using the previously calculated value as the new prior month value. Here is what it would look like:

Month Year	% Accepted	% Accepted Prior Month	Avg % Change	Forecast
Apr 2023	Null	Null	-2.00	10.03
Mar 2023	Null	Null	-1.5	12.03
Feb 2023	Null	Null	2.00	13.53
Jan 2023	Null	10.3	1.23	11.53
Dec 2022	10.3	11.28	-3.1	10.3
Nov 2022	11.28	14.5	2.14	11.28

Here is the measure I've built to this point:

Forecast = 
VAR _CurrentValue = [% Accepted]
VAR _ProjectedValue = [% Accepted Prior Month] +  [Avg % Change]

RETURN
IF(ISBLANK(_CurrentValue), _ProjectedValue, _CurrentValue

And it gives me this:

Month Year	% Accepted	% Accepted Prior Month	Avg % Change	Forecast
Apr 2023	Null	Null	-2.00	-2.00
Mar 2023	Null	Null	-1.5	-1.5
Feb 2023	Null	Null	2.00	2.00
Jan 2023	Null	10.3	1.23	11.53
Dec 2022	10.3	11.28	-3.1	10.3
Nov 2022	11.28	14.5	2.14	11.28

I can get one month forecasted, but after that it just returns the [Avg % Change], because it doesn't see the previous [Forecast] value as the prior month value.

I also tried creating a measure that was just [% Accepted Prior Month] + [Avg % Change], and a Prior Month version of that measure, but I still ended up with the same result.

Solution

DAX isn't recursive - in this case, you'll need to get the latest % Accepted and add the cumulative sum of Avg % Change.

Try the following (making a few assumptions on your DateTable):

Forecast = 
  var thisMonth = MAX('DateTable'[Date])
  var maxAvailableDate = MAXX(ALL('DateTable'), IF(NOT ISBLANK([% Accepted]), [Date])
  var lastAccepted =
    CALCULATE(
      [% Accepted],
      REMOVEFILTERS('DateTable'),
      'DateTable'[Date] = maxAvailableDate 
    )
  var fcstMonths = 
    FILTER(
      ALL('DateTable'),
      EDATE(maxAvailableDate, 1) <= 'DateTable'[Date] && 'DateTable'[Date] <= thisMonth
    )
  var cumulativeChange = 
    SUMX(
      SUMMARIZE(fcstMonths, 'Datetable'[MonthYear], "avg", [Avg % Change]),
      [avg]
    )

  var frcst = lastAccepted + cumulativeChange
  var curValue = [% Accepted]

  return COALESCE(curValue, frcst)