Using Prior Month Data to Build Future Month Data

[Anonymous]

I have three tables. One of inventory, one for demand, and one for anticipated orders. Inventory is simply the current amount of inventory held. Demand is the amount anticipated to be bought every month on average for the next 12 months. Anticipated orders are the amount the company anticipates buying each month to resupply inventory.

The goal of this DAX equation is to see if I have 10 pieces of inventory at the start, and each month I expect two lose 2 pieces, when will I run out before resupply? At the end of the month, the total would then be the new total for the next month. In excel, using formulas I would get the following:

Month	Example	1	2	3	4	5	6	7	8
Inventory	A	10	8	6	4	2	12	10	8
Demand	B	2	2	2	2	2	2	2	2
Order	C	0	0	0	0	12	0	0	0
Next Month	A-B+C = Next Months A	8	6	4	2	12	10	8	6

My Demand, Order, and Inventory Table share a unique identifier. All tables have a date to relate back to a calendar table.

I am struggling to get the dax to work where it reflects from one month to another, then to pull from the prior month after it has been calculated.

[lbendlin]

This is a cumulative function so you need to use one of the *X DAX features like SUMX , and table variables to hold the intermediate results.

Have you considered doing this in Power Query instead? It has a List.Accumulate() function specifically for this scenario.