Circular Dependency Open-Close Balance

Question

I am trying to generate a Balance, but I get a circular dependency. The Initial Balance (the day the report is consulted will be Day 1) is the sum (Table1[Qty]). Then from second day on, it will be Final Balance Sales, Sum Table2[Qty Sold] of the corresponding day Final Balance, it will be Initial Balance - Sales.

Also, the visual is a matrix and it should be possible to get to see the values of Initial Balance, Sales and Final Balance per item.

What im looking for:

Dax:

 Inventory = Calculate(
    SUM ( 'Table1'[Qty] ))





Sales = Calculate(
    SUM ( 'Table2'[Qty Sold] )
)



Initial Balance = IF(
    MAX(Dates[Date]) = TODAY(),
    [Inventory],
    CALCULATE([FinalBalance],PREVIOUSDAY(Dates[Date])
))



Final Balance = IF(
    MAX(Dates[Date]) = TODAY(),
   [Inventory] - [Sales],
   [Initial Balance] - [Sales]
)

Is there any way to work with this? Because, in the end, one measure depends on the other.

Answer 1

Think about it this way:

• Final Balance = SUM QTY (to date) - SUM Sales (to date)
• Initial Balance = SUM QTY (to date) - SUM Sales (up to excluding to date)

Try:

Initial Balance = 
  var thisDate = MAX(Dates[Date])
  var vSales =
    CALCUALTE(
      [Sales],
      REMOVEFILTERS(Dates),
      'Table2'[Date] < thisDate // note <
    )
  return [Inventory] - vSales



Final Balance = 
  var thisDate = MAX(Dates[Date])
  var vSales =
    CALCUALTE(
      [Sales],
      REMOVEFILTERS(Dates),
      'Table2'[Date] <= thisDate // note <=
    )
  return [Inventory] - vSales

Both measures above are independent of each other.