cancel
Showing results for
Did you mean:
Helper I

## Avoiding Circular Discrepancies for Col A = Col B (last row) and Col B = Col A (current row)?

My goal: To use DAX to calculate Ending Inventory as a function of MAX(0, Beginning Inventory + Receipts - Forecast), for every day and every SKU.

My Issue: I do not know how to lookup the previous day's Ending Inventory without PowerBI declaring a Circular Discrepancy.

Clarification: Because Ending Inventory rounds up to 0 at the end of each day if the formula is negative, I cannot collapse my inventory calculation into a single column and subtract all forecast up to a given date and add all receipts up to a given date like this:

``````(PseudoCode)
Invalid Projected Inventory =
Max(
0,
LOOKUPVALUE(Current Inventory)
+ CALCULATE(SUM(Receipts.QTY), Receipts.Date <= Date, Receipts.Item = Item)
- CALCULATE(SUM(Forecast.QTY), Forecast.Date <= Date, Forecast.Item = Item)
)``````

The reason I cannot use that single Projected Inventory column is to consider the following example:

• Inventory for today (Oct 14) is 100
• Total sum of Forecast thru Oct 20 is 200
• Receipt of 100 will arrive Oct 21
• Forecast on Oct 21, 2021 is 40

Desired Output: Projected Inventory on Oct 21 is 60

• Inventory at start of Oct 21 is 0 (100 current inventory - 200 forecast, rounded up to 0)
• Receipt of 100 on Oct 21 - Forecast of 40 on Oct 21 = Projected Inventory of 60

Output of Invalid Formula: Projected Inventory = 0

• Current Inventory of 100 - forecast thru Oct 21 of 240 + receipts of 100 = -40
• Projected Inventory = Max(0, -40) = 0

Here is a screenshot of all the data I'm using in my test model (text data at the bottom), including my desired input which I can get working in Excel.

And here is the same setup in PowerBI:

But when I try to use DAX to calculate the Beginning and Ending inventory of each day, I get a circular discrepancy. Is there any way around this within DAX?

Here are my formulas for the calculated table:

Table Source:

``Daily Inventory = GENERATe(distinct('Date Table'[Date]),  DISTINCT(Items[Item ID]))``

Beginning Inventory:

``````Beginning Inventory =
if('Daily Inventory'[Date]=today(),
calculate(sum('Current Inventory'[Current Inventory]),filter('Current Inventory', 'Current Inventory'[Item ID]=[Item ID])),
LOOKUPVALUE('Daily Inventory'[Ending Inventory],'Daily Inventory'[Item ID], [Item ID], 'Daily Inventory'[Date], [Date]-1)
)``````

Receipts:

``Receipts = CALCULATE(sum(Receipts[QTY]), filter(Receipts, Receipts[Date]=earlier([Date])), filter(Receipts,Receipts[Item ID]=earlier([Item ID])))``

Forecast:

``Forecast = CALCULATE(sum(Forecast[QTY]), filter(Forecast, Forecast[Date]=earlier([Date])), filter(Forecast,Forecast[Item ID]=earlier([Item ID])))``

Ending Inventory:

``Ending Inventory = max([Beginning Inventory] + [Receipts] - [Forecast],0)``

And here are my data tables:

Date Table

 Date 10/14/2021 10/15/2021 10/16/2021 10/17/2021 10/18/2021

Items

 Item ID Item Name 1 TestSKU1 2 TestSKU2

Current Inventory

 Item ID Current Inventory 1 100 2 100

Forecast

 Item ID Date QTY 1 10/14/2021 14 1 10/15/2021 12 1 10/16/2021 3 1 10/17/2021 24 1 10/18/2021 16 2 10/14/2021 43 2 10/15/2021 46 2 10/16/2021 7 2 10/17/2021 22 2 10/18/2021 32

Receipts

 Item ID Date QTY 1 10/17/2021 200 2 10/17/2021 200
2 REPLIES 2
Super User

See if this explanation from the gurus at SQLBI helps:

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Helper I

Thank you for the video. Unfortunately it does not appear to solve my issue (I tried all 3 solutions to make sure). The video seems focused on finding more indirect-hidden circular depencies, whereas my issue could be classified as a "direct-visible circular discrepancy".

In other words, my calculated table has Column A referencing Column B, and Column B references Column A. But what's frustrating is that PowerBI seems to flag this as an issue, despite my code filtering out true discrepancies by making the reference sequential - by that I mean that Column A references column B from the previous row, and Column B references Column A from the current row.

Here's an example in Excel, to help illustrate what I mean:

Excel handles this just fine when I set up a test example. I need a way to achieve the same goal within PowerBI, because all of the fields I need to use for the calculation is generated in PowerBI.

Announcements

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

#### What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.