Circular Dependency

[hongyuliu]

Hello,

I have monthly rolling forecast for all subsidairies. Raw data (Column A~E) is like following. G3, H3 & I3 were calculated by other formulas. Please advise how to calculate rest cells for Column G~I. I have more than one Product and Subsidiaries.

Thank you.

 

[lbendlin]

This is only possible in Power Query with List.Accumulate.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[hongyuliu]

Hi. Below is the sample data. Please advise how to solve it in Power Query. Thank you very much

Product	Company	Month	Forecast Due	This month Forecast	Previous Month Forecast	M0 C/F	M0 Rem	After M0 FC
Car 1	AAA	Month 01	1/1/2024	2
Car 1	AAA	Month 01	2/1/2024	1	2	1	0	0
Car 1	AAA	Month 02	1/1/2024	2
Car 1	AAA	Month 02	2/1/2024	1	2	0 =IF(E5>=H3,H3,E5)	0 =H3-G5	1 = E5-G5
Car 1	AAA	Month 03	1/1/2024	2
Car 1	AAA	Month 03	2/1/2024	7	2	0 =IF(E7>=H5,H5,E7)	0 =H5-G7	7 = E7-G7
Car 1	AAA	Month 04	1/1/2024	1
Car 1	AAA	Month 04	2/1/2024	1	1	0 =IF(E9>=H7,H7,E9)	0 =H7-G9	1 =E9-G9
Car 1	AAA	Month 05	1/1/2024	1
Car 1	AAA	Month 05	2/1/2024	1	1	0	0	1
Car 1	AAA	Month 06	1/1/2024	1
Car 1	AAA	Month 06	2/1/2024	1	1	0	0	1
Car 1	AAA	Month 07	1/1/2024	12
Car 1	AAA	Month 07	2/1/2024	4	12	0	0	4
Car 1	AAA	Month 08	1/1/2024	3
Car 1	AAA	Month 08	2/1/2024	3	3	0	0	3
Car 1	AAA	Month 09	1/1/2024	3
Car 1	AAA	Month 09	2/1/2024	4	3	0	0	4
Car 1	AAA	Month 10	1/1/2024	1
Car 1	AAA	Month 10	2/1/2024	0	1	0	0	0
Car 1	AAA	Month 11	1/1/2024	0
Car 1	AAA	Month 11	2/1/2024	0	0	0	0	0
Car 1	AAA	Month 12	1/1/2024	0
Car 1	AAA	Month 12	2/1/2024	0	0	0	0	0
Car 2	BBB	Month 01	1/1/2024
Car 2	BBB	Month 01	2/1/2024

[lbendlin]

Why is H3   resulting in 0 and not in -1 ?

[hongyuliu]

Hi,

Actually, there is another column to the left of "M0 C/F", called "Remainder".

H3 is Remainder - M0 C/F, so it's 1-1=0.