Starting and closing balances generate circular reference

[D_Gibson]

I am attempting to build a report in Power BI to display inventory balances for selected SKUs for the current day through to 14 days into the future. On any given day I am interested in calculating projected closing balances based on starting inventory plus or minus a number of ins and outs. Starting balance for the subsequent day will be equal to the closing balance from the previous day. I.e.:

StartingBalanceMeasure = if(max('BalancesMatrix'[BalanceDate])=[today],[inventory],[PreviousDayClosingBal])

Where 

ClosingBal = if(max('BalancesMatrix'[BalanceDate])=[today], [Inventory]+[Production]-[Outloads]-[ExpiringStock], [StartingBalanceMeasure]+[Production]-[Outloads]-[ExpiringStock])

And

PreviousDayClosingBal = calculate([ClosingBal], PREVIOUSDAY('BalancesMatrix'[ReportDate]))

And

Today = datevalue(FORMAT(TODAY(), "DD/MM/YYYY"))

Performing the same exercise within Excel is a relatively trivial exercise, but doing so within Power BI generates a 'circular reference' error. 

Any guidance anyone can give is appreciated.

[v-jianboli-msft]

Hi @D_Gibson ,

Excel performs calculations on cells, which are referenced by coordinates, so you can easily implement your calculations in Excel.
DAX is different, the concept of cells and coordinates do not exist in DAX. dax deals with tables and columns, not cells.

In your calculation logic: the calculation of StartingBalanceMeasure involves PreviousDayClosingBal, and the calculation of PreviousDayClosingBal involves StartingBalanceMeasure, so a circular dependency arises. If you want to implement this calculation, the easiest way is to do it via Excel and then import it into PowerBI. If you want to do it directly in PowerBI via the DAX calculation, you may need to completely replace your calculation and split it into many steps (which is a very complex project). These are the result of the calculation logic of Excel and DAX.
I hope my explanation will make sense to you.

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[D_Gibson · Accepted solution]

Thank you again Jianbo. Although I'm not an expert on the DAX language I know that row-context is a fundamental part of the DAX language, in which case row co-ordinates are important. Whenever a measure is placed in a table it will be evaluated against each row in the table according to any defined relationships. It is not simply calculated once for the column or table. 

Perhaps it would be more accurate to say that 'DAX does not evaluate row-context before deciding whether there is a circular dependency'. 

If DAX were to evaluate row-context there would be no dependency between my StartingBalanceMeasure and PreviousDayClosingBal.

In any case, a colleague has suggested a different approach and it has done the job nicely. Essentially, starting and closing balance are calculated independently of each other using the VAR function. A total of 9 lines of code is required for each. Below is the starting balance calculation: 

Weight (Starting Balance) =

VAR vD = MAX('Date (Reporting)'[Date])

Var vBalance = Calculate(SUM('Balances'[Weight]),'Balances'[Source] = "Inventory")

var vProd = Calculate(SUM('Balances'[Weight]),'Balances'[ArrvlDate] < vD, 'Balances'[Source] = "Production")

var vSupply = CALCULATE([Weight (Supply)], ALL('Date (Reporting)'),'Date (Reporting)'[Date] < vD)

var vTransOut = CALCULATE([Weight (Transfers Out)],ALL('Date (Reporting)'),'Date (Reporting)'[Date] < vD)

var vTransIn = CALCULATE([Weight (Transfers In)],ALL('Date (Reporting)'),'Date (Reporting)'[Date] < vD)

var vExpire = CALCULATE([Weight (Expire)],ALL('Date (Reporting)'),'Date (Reporting)'[Date] < vD)

 return vBalance + vProd -vSupply +vTransIn- vTransOut - vExpire

[v-jianboli-msft]

Hi @D_Gibson ,

Excel is cell-based, unlike excel, power bi is not cell-based, so this error occurs

If two objects depend on each other, the engine does not know which one to compute first. If A depends on B and – at the same time – B depends on A, then it is impossible to compute A nor B. As soon as you compute A, this triggers the refresh of B. So you start refreshing B, wich in turn triggers the refresh of A. You are stuck in an infinite loop with no hope of ever getting the job done.

For more details, please refer to:

Understanding circular dependencies in DAX - SQLBI 

Avoiding circular dependency errors in DAX - SQLBI 

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[D_Gibson]

Thank you Jianbo for your response. Yes, I am aware of what a circular reference is and that Excel is cell based.

I note that your response has been marked as an accepted solution eventhough I have not endorsed it.

If you examine my code provided above you will see there is no circularity in my logic. Starting balance for period T equals closing balance for period T-1. If this is impossible to calculate it wouldn't be possible to calculate it in Excel either. You seem to be suggesting that if tool A isn't smart enough to do something it's impossible and the logic is flawed, eventhough tool B can do it very easily. 

I have reviewed the article on sqlbi.com but don't believe the circumstances are the same as my use case and I don't believe the explanations will solve my error. 

My question was in relation to the code in my original post. Do you have any pointers on a way to adjust the code I posted above to avoid Power BI getting upset about a circular reference ?