Recursive calculation

[Anonymous]

Hi,

I have the following data

- Opening Volume

- Delivered Volume

- Sales Volume

- Estimated Volume

and would like to create a table with the following calculated columns:

- Opening Volume (N) = Closing Volume (N-1)

- Adjusted Volume (N) = Opening Volume (N+1) - [Opening Volume (N) + Delivered Volume (N) - Sales Volume (N)]

- Closing Volume (N) = Opening Volume (N) + Delivered Volume (N) - Sales Volume (N) + Adjusted Volume (N)

where N = Today, N-1 = Yesterday, and N+1 = Tomorrow

After trying with DAX, I am getting the circular dependency error in Power BI. Does anyone know of a workaround or have any suggestions on how best to approach the above?

Thanking you all in advance. 

[Anonymous]

Hi @Anonymous ,

You have already had Opening Volume in your table, why you want to use Closing Volume (N-1) to get the Opening Volume(N)?

Best Regards,

Jay

[Anonymous]

Hi @Anonymous,

I have the initial value of Opening Volume, but for subsequent Opening Volumes, I am required to use the Closing Volume from the previous day. If you refer to my screenshots in https://community.powerbi.com/t5/Desktop/Recursive-calculation/m-p/2276413/highlight/true#M826076, that might help explain things better. 

Regards,

Alex

[bcdobbs]

I'd start with 
Closing Volume (N) = Opening Volume (N) + Delivered Volume (N) - Sales Volume (N) + Adjusted Volume (N) 

given they're all today it should just work inside the existing row context.

I'm then assuming you have a date column so should be able to use CALCULATE to find the values for previous days. however you'll have to include ALLEXCEPT to stop your new columns going into context transition:
Avoiding circular dependency errors in DAX - SQLBI

If you share a demo pbix I can help put that into action.

[Anonymous]

Hi @bcdobbs, sorry, I don't seem to have the ability to upload a pbix file. 

I will try to explain using the images below and I hope this makes sense:

- my source data are columns A, B, E, F, G, and H

- values in row 1 for columns C and D are static

- the remainder rows (row 2 onwards) of columns C and D, and columns J, K, L, M, N, P and Q are calculated with the formula displayed in the header

What I am having difficulty with is to have the values calculated in column P (row n-1) populated into column D (row n). Similarly, I would like values calculated in column Q (row n-1) populated into column C (row n). Please see the highlighted texts in blue and green. 

Any guidance on how to approach the above problem would be greatly appreciated. Thanks.