Forum Discussion
Creating a measure that builds upon itself, using a fixed starting point
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...
Community ChampionAnonymous 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
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.