Creating a measure that builds upon itself, using a fixed starting point

[cfrancart]

I have a requirement to report projected month end inventory using month end inventory from the previous month adjusted by monthly forecast data. In other words, I am using January month end inventory (coming from a table - it's an actual value) and adjusting that to get to the projected February month end on hand inventory. Where I am struggling is coming up with the future month end values because the measure is circular (month end inventory for February + forecast changes for March = month end inventory for March, then month end inventory for March = forecast changes for April = month end inventory for April, etc.). It's an easy solution in Excel using relative cell locations, but the data is coming from a direct query to an AAS model so functions like Earlier or adding an index column to a calculated table and filtering based on MAX values won't work as have been used in Stock Coverage and other measures I've found in the community. This is an example of what the data looks like; the Projected/Actual value for January is my starting point - adding that to the Demand and Adjustments for February would be the Projected/Actual value for February and that would be used to calculate March. I'm literally 4 days into this and haven't come close to a solution. Anyone have any suggestions? 

Month	Demand	Adjustments	Projected/Actual
6/1/2023	229,294,368	(755,536)
5/1/2023	200,754,666	(545,455)
4/1/2023	179,236,325	(633,031)
3/1/2023	200,909,548	(811,407)
2/1/2023	131,899,830	(519,943)
1/1/2023	143,932,302	(580,681)	155,347,010

[cfrancart · Accepted solution]

@Greg_Deckler - with some assistance from a brilliant resource on my team, we came up with a solution. Instead of using MAX, we utilized a couple of variables in order to evaluate the sums of the demand and adjustment columns based on the dates between the beginning month (where the last inventory is known) and simply adding those sums to the beginning inventory. So for Feb, it's the sum of the demand/adjustment where month start > jan 2023 and <= feb 2023 plus the beginning inventory. For April, it's the sum of the demand/adjustment where month start > jan 2023 and <= apr 2023 plus the beginning inventory and so on. I can't believe it was so simple...

[Greg_Deckler]

@cfrancart Maybe this? PBIX is attached below signature.

Measure = 
    VAR __Month = MAX('Table'[Month])
    VAR __Table = FILTER(ALL('Table'), [Month] < __Month)
    VAR __Projected = SUMX(FILTER(ALL('Table'),[Month] = DATE(2023,1,1)),[Projected/Actual])
    VAR __Demand = SUMX(__Table,[Demand ])
    VAR __Adjustments = SUMX(__Table, [Adjustments])
    VAR __Result = __Projected + __Demand + __Adjustments
RETURN
    __Result

[cfrancart]

Hi Greg - I should have mentioned that the data in the Demand, Adjustments and Projected/Actual columns are coming from three different tables in the AAS model (there are actually 2 models in this hybrid, just to add another level of complexity). I tried applying your logic to a calculated table but DAX doesn't like trying to refer to a max value of a column in a calculated table. I'm trying to find out where the actual fields are coming from in the model, and perhaps do direct SQL queries to build these columns into a single table, and then your logic will probably work.