Forum Discussion
Starting and closing balances generate circular reference
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
Community SupportHi 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.
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