measures that effectively need to circulate dependency

[jdtobon88]

Regards,

We are trying to develop a MPS (Master Production Schedule) through power pivot and now we are facing a constraint related to a circular dependency which is not an issue when working Excel tables but in DAX we have not found a way to overcome this point. In words, we are trying to calculate an initial stock depending on the final stock in the previous period, however for the first period, the initial stock is given in a current stock table, then from a strictly logic point of view there is not a circular dependency, but from the DAX logic in the functions we are trying to use there is effectively a circular dependency.

In the Excel file we have the Excel table example and the data model in power pivot until the point we are stuck.

The way we thought it would be possible is using the this formula for the Initial stock column:

Initial stock=VAR Period=[Period]
VAR Ref=[Product]
RETURN
IF([Period]=1;[Current Stock];
CALCULATE(SUMX(Tbl_Forecast;[Final Projected Stock]);FILTER(Tbl_Forecast;[Product]=Ref&&[Period]=Period-1)))

While the function for final projected stock column would be:

Final Projected Stock=[Initial stock]-[Forecast]+[Transit stock]+[New requirement]

I share the excel attachment with the model in dropbox

https://www.dropbox.com/scl/fi/w3gz792t47ifkvpjjgnnz/MPSModel.xlsx?dl=0&rlkey=uy8srwxssugzq25wnz1gx6...

[AlexisOlson]

Unfortunately, recursion is not possible in DAX.

There are sometimes workarounds where you refactor your logic to be non-recursive. (See the links at the bottom of my StackOverflow answer for a couple more examples.)