cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Funk-E-Guy
Helper I
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.

 

PowerBI Rolling Daily Inventory.png

 

And here is the same setup in PowerBI:

PowerBI Rolling Daily Inventory in PBI.png Daily Inventory Relationships.png

 

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?

 

Beginning Inventory.png

 

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 IDItem Name
1TestSKU1
2TestSKU2

Current Inventory

Item IDCurrent Inventory
1100
2100

Forecast

Item IDDateQTY
110/14/202114
110/15/202112
110/16/20213
110/17/202124
110/18/202116
210/14/202143
210/15/202146
210/16/20217
210/17/202122
210/18/202132

Receipts

Item IDDateQTY
110/17/2021200
210/17/2021200
2 REPLIES 2
PaulDBrown
Super User
Super User

See if this explanation from the gurus at SQLBI helps:

https://youtu.be/m-qhcDcNKrE 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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:

 

Sequential Reference.png

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.




Helpful resources

Announcements
Microsoft Build 768x460.png

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_carousel_with_text (1).png

Charticulator Design Challenge

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

May UG Leader Call Carousel 768x460.png

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.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!