DAX: Cyclic Dependency - calculating Backlog and Rollover

[Jaed]

Hi all,

I am not vey good at dax formulas and really need your help. Please see below my excel file for calculating rollovers and backlog on a daily level. 

Unfortunately this requires getting values from the previous day which depends on the calculated values giving an cyclic dependency error.  

Given Data is (see sample Data at the bottom):

- Date

- Outbound Volume

- Forecasted Volume

- Shipped Volume

Columns which need to be calculated:

1. Cyclic dependency

- Total Workload = Outbound Volume + Rollover Previous day

- Rollover = If(Total Workload - Forecasted Volume <0,0, Total Workload - Forecasted Volume)

2. Cyclic Dependency

- Daily Workload = If(Total Workload > Forecasted Volume, Forecasted Volume, Total Workload)

- Daily Workload + Backlog = Daily Workload + Backlog Previous day

- Backlog = If( Daily Workload + Backlog - Shipped Volume <0, 0, Daily Workload + Backlog - Shipped Volume)

Is there a solution to this or a better way to approach. Unfortunately I am aggregating the given Data in DAX and it is not available in Power Query.

Appreciate any help from you folks

BR

James

Sample Data:

Date	Outbound Volume	Forecast	Shipped Volume
06.03.2023	35.445	18.750	12.864
07.03.2023	9.150	18.750	15.915
08.03.2023	17.585	18.750	15.297
09.03.2023	9.132	18.750	12.788
10.03.2023	6.381	18.750	8.843
11.03.2023	-	-	5.976
12.03.2023	10	-	-
13.03.2023	17.991	18.750	10.159
14.03.2023	15.441	18.750	13.064
15.03.2023	15.167	18.750	14.356
16.03.2023	6.837	18.750	11.320
17.03.2023	11.910	18.750	30.608
18.03.2023	6	-	7.698
19.03.2023	-	-	-

[Anonymous]

Hi @Jaed ,

Could you please copy and paste the sample data into the forum instead of taking a screenshot.
You seem to be experiencing a circular dependency issue with Dax. Could you share your dax code and expected output? It would be very helpful, thanks!
You can also consider using M-code in PowerQuery to create custom columns to solve this problem.

How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

About circular dependency:

Understanding circular dependencies in DAX - SQLBI
Avoiding circular dependency errors in DAX - SQLBI

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Jaed]

Hope the Data provided can be copy / pasted into excel. Thanks for your help 🙂