metrics that depend on others and are recalculated

[jdtobon88]

Greetings

I have a novelty with some calculations in dax since the result gives me a cyclical dependency.
Turns out I need that:
The measure Inv. Initial available when the month is 1 bring me the available inventory, otherwise you must bring me the value of the following month in the order of the month of the Final Available Inventory, but to calculate this final available inventory I must find the initial balanced requirements, these are calculated based on that initial available inventory that must be recalculated.
I share the heading of how the formulas are raised:

Inv. Disponible inicial = IF(ordermonth=1;Inv. Disponible;Inv. Disponible final)

Requerimientos brutos= MIN(1;ROUND(DIVIDE(([Inv. Disponible final objetivo]-( [Inv. Disponible inicial]-[Forecast Ventas]+ [CantidadenTransito])); [MOQ];0);0))* [MOQ]

Requerimientos Balanceados = max(0; [Requerimientos brutos]+ROUND(DIVIDE(max(0;([Inv. Disponible final objetivo]-( [Inv. Disponible Inicial - Balance 1]-[Forecast Ventas]+ [CantidadenTransito]))- [MOQ]); [Cant. Empaque];0);0)* [Cant. Empaque])

Inv. Disponible final =VAR Periodo= 'MPS Fenix'[Orden Mes]

Var Referencia= 'MPS Fenix'[REFERENCIA ACTUAL]

Return

MAX(Inv. Disponible inicial + CALCULATE(sumx(Table; [CantidadenTransito]+[ Requerimientos Balanceados]- [Forecast Ventas]);FILTER(ALL(Table); [REFERENCIA ACTUAL]=Referencia && [Orden Mes]<=Periodo));0)

This image shows us the information and the desired result in red:

thanks

[AlexisOlson · Accepted solution]

You cannot recursively define columns in DAX but sometimes there are workarounds using cumulative totals.

For examples of workarounds, take a look here and at the linked posts here.

[v-shex-msft · Accepted solution]

Hi @jdtobon88,

Did these calculations work with simple cumulative on your records or complex recursive? AFAIK, power bi data model table does not include row and column index so they can't do the recursive calculation as excel worksheets.
I'd like to suggest you do these calculations in excel and get the result records to power bi to design visualizations.

Previous Value (“Recursion”) in DAX – Greg Deckler
Regards,

Xiaoxin Sheng

[AlexisOlson · Accepted solution]

You cannot recursively define columns in DAX but sometimes there are workarounds using cumulative totals.

For examples of workarounds, take a look here and at the linked posts here.

[TomMartens]

Hey @jdtobon88 ,

maybe this article and the available pbix provides ideas on how to tackle your challenge: Using Table Iterators to calculate a future value - Mincing Data - Gain Insight from Data (minceddat...

If not, take the time to create an xlsx file that contains sample data that reflects your data model (tables, relationships, calculated columns). Add a sheet that uses the values from the sample data and use Excel-Formulas to create the result you need. Upload the file to onedrive or dropbox and share the link.

Regards,

Tom

[jdtobon88]

Hello, thanks for you answer.

this is the file https://www.dropbox.com/s/s3hi6dl8sqbvo6y/cycli.xlsx?dl=0

[jdtobon88]

please help me @TomMartens 

[TomMartens]

Hey @jdtobon88 ,

I will have a closer look at the file you provided the next week.

I have to admit that I was expecting data that can easily be imported.

Regards,

Tom