Opening & Closing Balances

DAX DAX Calculations
1

Hi there.

I am trying to forecast inventory levels based on assumed sales (4-month average sales as at the end of March) and purchases that will be arriving.

All amounts are measured in KG’s.

I am struggling with the DAX formula to get my closing balance of my inventory to work out correctly.

The opening balance is equal to the closing balance of the previous month (except for April - this is the actual inventory level at the beginning of the month).

I need the closing balance to be worked out as follows: opening balance + stock change). When I try this, I get an error saying there is a circular dependency. I have therefore had to use another column just to get the visual to show.

Please can someone help me!!

Here is the pbix file:
dummy pbi.pbix (364.1 KB)

2

Hi @Oliver ,

Did you find solution?

Maybe you can check this workaround:

Best regards

3

Hi @Oliver,

It seems you’re encountering a common issue with DAX when trying to calculate a running total or closing balance due to circular dependency. Here’s a solution to calculate the closing balance of your inventory correctly.

  1. Create a Calculated Column for the Opening Balance:

This column will take the closing balance of the previous month as the opening balance for the current month.

DAXCopy codeOpening Balance = IF(    'Table'[Month] = "April",     'Table'[Actual Opening Balance],     CALCULATE(        MAX('Table'[Closing Balance]),        FILTER(            'Table',            'Table'[Month] = EARLIER('Table'[Month]) - 1        )    ))
  1. Create a Measure for the Closing Balance:

This measure will calculate the closing balance based on the opening balance and stock changes.

DAXCopy codeClosing Balance = SUMX(    'Table',    'Table'[Opening Balance] + 'Table'[Stock Change])
  1. Adjust the Calculation to Avoid Circular Dependency:

If the above doesn’t resolve the circular dependency, consider using variables and separate calculations to avoid direct dependencies within the same context.

Here’s a more robust example incorporating variables:

DAXCopy codeClosing Balance = VAR CurrentMonth = MAX('Table'[Month])VAR OpeningBalance =     IF(        CurrentMonth = "April",         MAX('Table'[Actual Opening Balance]),         CALCULATE(            MAX('Table'[Closing Balance]),            FILTER(                'Table',                'Table'[Month] = CurrentMonth - 1            )        )    )RETURN    OpeningBalance + SUM('Table'[Stock Change])

In case these steps do not fully resolve your issue, I recommend uploading your query and model details on the Data Mentor platform for more detailed analysis.

Kind regards,

Enterprise DNA Support Team

1 Like