Trying to duplicate a PQ/excel report in PBI. Circular reference exercise.

[dwoolaver]

Hi, I wrote up a really long expalination to my scenario. But, instead of posting a ridiculousy long post perhaps a picture is worth a 1,000 words.  The question is what method should  be used (if it is even possible) to recreate the below excel in PBI. The challange is two columns depend on each either other BUT it seems to work in excel because the table is first soreted in specific manner in PQ before the formulas run.  The table in excel is one flat table with onhand,sales and forecast data but I easier can create 3 normalized tables to join with a date table instead.  Essenitally the excel is doing something like delcaring a table var in dax. 

SUMS=IF(LEFT([@REFTYPE],6)="Invent",SUMIFS([Sales Orders],[ITEMID],[@ITEMID],[REQDATE],"<"&[@[FINAL Best Before Date]])+SUMIFS([Forecast],[ITEMID],[@ITEMID],[REQDATE],"<="&[@[FINAL Best Before Date]])-SUMIFS([Quantity Allocation],[ITEMID],[@ITEMID],[FINAL Best Before Date],"<"&[@[FINAL Best Before Date]]),"")

Quantity Allocation =IF(LEFT([@REFTYPE],6)="Invent",IF([@Sums]>[@[On Hand]],[@[On Hand]],[@Sums]),"")

A classic chicken or the egg but because of the sort order in excel  and date qualifiers it works in excel without throwing a circular reference error.  OK that is still a long post but far less than my first version.  

[lbendlin]

usually things like these are possible in Power Query but not in DAX.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[dwoolaver]

Thank you for the fast reply.

Here is a paired down data example.  The values in "Sums" and "Quantity Alloation" are the excpected answers. 

These are not straightforward calcultions They are iterative based one the sort order of the table Item/ reftype asc /Best By Date asc/reqdate asc

In words:

[Sums] = when the REFTYPEis InventOnHand  sum up the [sales orders] for the same item where the Reqdate is < the [Best By Date] and then subtract the sum of the [Quantity Allocation] where the [Best by Date] < the current  (current line that is)  [Best By Date] 

With [Quantity Allocation] in turn being defined as 

= when the REFTYPE is InventOnHand then IF([@Sums]>[@[On Hand]],[@[On Hand]],[@Sums]),"")

or in other words if Sums is > {On Hand] show [On Hand] if not not show [Sums]

ITEMID	Best Before Date	REQDATE	SO#	REFTYPE	On Hand	Sales Orders	Forecast	Sums	Quantity Allocation
000035	4/10/2025	1/1/1900		InventOnHand	746			8	8
000035	4/11/2025	1/1/1900		InventOnHand	494			3	3
000035	4/25/2025	1/1/1900		InventOnHand	29			3	3
000035	1/1/1900	3/28/2025	W01977883	Sales		1
000035	1/1/1900	4/7/2025	68281887	Sales		1
000035	1/1/1900	4/8/2025	W01973821	Sales		4
000035	1/1/1900	4/8/2025	W01974393	Sales		1
000035	1/1/1900	4/9/2025	W01982773	Sales		1
000035	1/1/1900	4/10/2025	W01983398	Sales		1
000035	1/1/1900	4/10/2025	W01974613	Sales		1
000035	1/1/1900	4/10/2025	W01983305	Sales		1
000035	1/1/1900	4/11/2025	W01983490	Sales		1
000035	1/1/1900	4/11/2025	W01983287	Sales		1
000035	1/1/1900	4/14/2025	W01983163	Sales		1

[lbendlin]

are you trying to allocate aging inventory to incoming sales orders?  Your On Hand numbers are high - what should happen when they are noth high enough to cover the demand?  Do Sales Orders have to be placed before the "Best Before Date" ?

These should be separate tables, one for inventory levels and one for sales. 

[dwoolaver]

Thanks, yes in my first post I said could seperate into proper tables but that alone does not solve the problem.  This report is to identify when there is too much of a product on hand for a particluar batch (aka best by date). Risk of having to be written off, etc.  The root issue they way they want the calculation done. 

Say you have two batches  batch A and B  

A has best by date of 5/5/2025 and there are 100 units on hand today.

B has best by date of 5/10/2025 and there are 100 units on hand. today.

In this context the "sales" activity is for not yet shipped sales orders and therefore the qty from sales has NOT yet been deducted from the on hand amounts yet. But the sales orders do have a requested ship date. So say we have sales with request shipdates like this

4/30/2025 25 units

5/1/2025 25 units. 

The sales are not in anyway assoicated with specific batch numbers. 

So in this case we want to deduct all 50 units from ONLY the first batch of 5/5/2025. leaving 50 units of risk for 05/05/2025 and 100 units of risk for 05/10/2025 . 

but simple rule of "give me all the sales before the best by date" does not work because all of the sales are before both best by dates. So even with data seperated in to seperate tables without some sort of itereative row by row function  the same problem exits. How do match the sales to ONLY the first best by batch. 

[v-priyankata · Accepted solution]

Hi  @dwoolaver 

In Power BI using a technique called "virtual table iteration with cumulative logic". Here’s a ways to achieve the same outcome in Power BI:

You want to Allocate sales and forecast quantities sequentially to inventory batches based on Best Before Date, respecting, ReqDate < Best Before Date and sort order: ItemID, RefType, Best Before Date, ReqDate

Try to Prevent sales/forecast from being double-counted across batches and output columns similar to Sums and Quantity Allocation.

Try to Normalize your Tables if required try to create separate fact tables

Inventory: ItemID, OnHand, BestBeforeDate, RefType = InventOnHand

SalesOrders: ItemID, Qty, ReqDate, RefType = Sales

Forecast: ItemID, Qty, ReqDate, RefType = Forecast

Include a shared ItemID and create a Date table linked to ReqDate and BestBeforeDate for time intelligence.

Now Define Sort Order

Use Power Query or DAX to assign a sequential index (like SortIndex) per ItemID for all batches (Invent rows only), sorted by BestBeforeDate asc. This emulates the "Excel row order" that makes circular logic possible.

Build a Cumulative Demand Allocator, using DAX, we create a cumulative running demand to simulate step-by-step allocation:

Cumulative Demand Before = 
VAR CurrentDate = 'Inventory'[BestBeforeDate]

VAR CurrentItem = 'Inventory'[ItemID]

RETURN
CALCULATE(SUM('SalesOrders'[Qty]) + SUM('Forecast'[Qty]),'SalesOrders'[ReqDate] < CurrentDate,'SalesOrders'[ItemID] = CurrentItem)

After creating a above measure now Subtract Prior Quantity Allocation

We now simulate cumulative Quantity Allocation by subtracting what's already been allocated to earlier batches:

Allocated Before =

VAR CurrentSortIndex = 'Inventory'[SortIndex]

VAR CurrentItem = 'Inventory'[ItemID]

RETURN

CALCULATE(SUM('Inventory'[Quantity Allocation]), FILTER(ALL('Inventory'),

'Inventory'[SortIndex] < CurrentSortIndex && 'Inventory'[ItemID] = CurrentItem))

Now Calculate Final Sums and Quantity Allocation

Sums = [Cumulative Demand Before] - [Allocated Before]

Quantity Allocation = 
VAR SumQty = [Sums]
VAR OnHand = 'Inventory'[OnHand]
RETURN
IF(SumQty > OnHand, OnHand, SumQty)

If You’re re-implementing what’s effectively a running allocation logic — a problem solvable via DAX iterators and virtual tables simulating loop-based accumulation. DAX does not truly loop row-by-row but with proper ordering and context via calculated columns or measures, we can make it behave as if it does.

Use a matrix or table visual showing:

And it will match your Excel results exactly, provided. SortIndex is correct and Allocation is calculated cumulatively and Filters respect time logic (ReqDate < BestBeforeDate).

If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you..