DAX:将库存分配给未结订单 [英] DAX : Allocate Inventory to Open Orders

问题描述

我正在尝试分配库存项目以在DAX，PowerBi中打开订单.这个想法是先将所有可用库存分配到最旧的订单，然后将剩余库存分配到第二个最旧的订单，依此类推，直到库存被耗尽.即使未完全满足未结订单，库存也可能耗尽.

I am trying to allocate inventory items to open orders in DAX, PowerBi. The idea is to allocate all the available inventory to the oldest order first, then allocate the remaining inventory to the second oldest, etc. until the inventory is depleted. The inventory can be depleted even if the open order is not completely satisfied.

请检查所附图片作为下面分配的示例

Please check the attached picture as an example for the allocations below

我尝试使用Calculate函数在未结订单表中添加一列，但结果根本不正确.它没有做任何分配，只是汇总了库存.

I tried adding a column in the open order table using the Calculate function but the results were not correct at all. It didn't do any allocations, it just summed up the inventory.

CALCULATE (
        [QOH],
        FILTER (
            ALL ( 'Open Orders'[Parent Name] ),
            'Open Orders'[Due Date] <= MAX ( ( 'Open Orders'[Due Date] ) )
        )
    )

推荐答案

这里最棘手的部分是计算当前行日期之前的订购顺序，但是您可以通过查看先前到期日期的总和来实现这一点.

The tricky part here is calculating what was ordered before the date on the current row but you can do that by looking at the sum for previous due dates.

这应该用作计算列:

Inventory Allocation =
VAR TotalInventory =
    LOOKUPVALUE ( Inventory[Qty On Hand], Inventory[Item], 'Open Orders'[Item] )
VAR AlreadyOrdered =
    CALCULATE (
        SUM ( 'Open Orders'[Qty Open] ),
        ALL ( 'Open Orders' ),
        Inventory[Item] = EARLIER ( 'Open Orders'[Item] ),
        'Open Orders'[Due Date] < EARLIER ( 'Open Orders'[Due Date] )
    )
RETURN
    IF (
        AlreadyOrdered > TotalInventory,
        0,
        MIN ( 'Open Orders'[Qty Open], TotalInventory - AlreadyOrdered )
    )

---

在 AlreadyOrdered 定义中， ALL 参数表示要从表中删除所有行/过滤器上下文，以下参数指定我想要的过滤器，并且等效到 REMOVEFILTERS 函数 CALCULATE 的代码.

In the AlreadyOrdered definition, the ALL argument says to remove all the row/filter context from the table and the following arguments specify the filtering I do want and is equivalent to the REMOVEFILTERS funcion inside of a CALCULATE.

EARLIER 函数常常使人感到困惑，因为它与日期无关，而是引用较早的行上下文，因此我可以在 CALCULATE 内编写条件时引用表的当前行中的值.您可以改用变量:

The EARLIER function is often confusing to people as it has nothing to do with dates but refers to the earlier row context so I can refer to the value in the current row of the table while writing a condition inside a CALCULATE. You could use variables instead:

Inventory Allocation =
VAR TotalInventory =
    LOOKUPVALUE ( Inventory[Qty On Hand], Inventory[Item], 'Open Orders'[Item] )
VAR CurrItem = 'Open Orders'[Item]
VAR CurrDate = 'Open Orders'[Due Date]
VAR AlreadyOrdered =
    CALCULATE (
        SUM ( 'Open Orders'[Qty Open] ),
        REMOVEFILTERS ( 'Open Orders' ),
        Inventory[Item] = CurrItem,
        'Open Orders'[Due Date] < CurrDate
    )
RETURN
    IF (
        AlreadyOrdered > TotalInventory,
        0,
        MIN ( 'Open Orders'[Qty Open], TotalInventory - AlreadyOrdered )
    )

这篇关于DAX:将库存分配给未结订单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！