如何使用Power BI DAX从移动表计算每天的库存？ [英] How to calculate inventory per day from movement table with Power BI DAX?

问题描述

我有一张清单，上面有库存动向。每个库存项目都有唯一的ID，并且它们会随着时间变化（状态A，B，C和D，但并非总是以此顺序）。 ID的每个状态更改都是表中带有状态更改时间戳的新记录。我的目标是使用Power BI DAX计算某一天处于状态 B 的库存数量。逻辑是计算唯一ID的数量，这些ID在特定日期之前违反状态 B，但在该日期之前没有任何新状态。

I have a table with inventory movements. Each inventory item has a unique ID and they change status overtime (let's say status A, B, C and D, but not always in this order). Each status change of an ID is a new record in the table with the timestamp of the status change. My goal is to calculate with Power BI DAX the number of inventory at a certain day in status 'B'. The logic is to count the number of distinct IDs, which breached status 'B' before the certain day but doesn't have any newer status before that day.

源表：

ID  |  TimeStamp  |  Status
1   |  8/20/2018  |    A
1   |  8/21/2018  |    B
1   |  8/24/2018  |    C
2   |  8/19/2018  |    A
2   |  8/20/2018  |    B
2   |  8/22/2018  |    C
2   |  8/24/2018  |    D
3   |  8/18/2018  |    A
3   |  8/21/2018  |    B
4   |  8/15/2018  |    A
4   |  8/17/2018  |    B
4   |  8/24/2018  |    D

输出表示例：

Date       |  Count of Items in Status B on this Day
8/17/2018  |     3
8/18/2018  |     2
8/19/2018  |     0
8/20/2018  |     8
8/21/2018  |     10
8/22/2018  |     5
8/23/2018  |     3

我正在考虑为最新时间戳创建一个表，每个ID的状态为 B，并且然后在状态'B'的时间戳之后查找下一个时间戳（如果适用）：

I was thinking of creating a table for the latest timestamp with status 'B' for each ID and then look for the next timestamp, after the timestamp of status 'B', if applicable:

ID (primary key)  |  TimeStamp of 'B' breached | TimeStamp of next status breach
1                 |     8/20/2018              |  8/21/2018
2                 |     8/18/2018              |  8/22/2018
3                 |     8/21/2018              |  
4                 |     8/15/2018              |  8/20/2018

然后我将上述数据插入Date上下文并计算上表中的ID，其中 违反了时间戳 值较小，而 <下一个状态违反了时间戳 值大于特定日期。

Then I would plug the above data into the Date context and count the number of IDs from the above table, where the "TimeStamp of 'B' breached" value is smaller AND the "TimeStamp of next status breach" value is greater than the certain date.

不幸的是，我不确定如何将此逻辑插入DAX语法中，因此任何建议都会受到赞赏。

Unfortunately I am not sure how to plug this logic into DAX syntax, hence any recommendations would be appreciated.

非常感谢！
Gergő

Thanks a lot! Gergő

推荐答案

这有点棘手，但是我们可以使用临时计算的汇总表来做到这一点在一个度量范围内：

This is a bit tricky, but we can do it with the use of a temporary calculated summary table within a measure:

CountStatusB =
    SUMX(
        ADDCOLUMNS(
            SUMMARIZE(
                FILTER(
                    ALL(Inventory),
                    Inventory[TimeStamp] <= MAX(Inventory[TimeStamp])
                ),
                Inventory[ID],
                "LastTimeStamp",
                MAX(Inventory[TimeStamp])
            ),
            "Status",
            LOOKUPVALUE(Inventory[Status],
                Inventory[ID], Inventory[ID],
                Inventory[TimeStamp], [LastTimeStamp])
            ),
        IF([Status] = "B",
            1,
            0
        )
    )

首先，我们创建一个汇总表来计算最后一个 TimeStamp ，每个 ID 值。为此，我们在过滤表上使用 SUMMARIZE 函数，在该表中，我们仅考虑来自当前日期或更早的日期，并按 ID ，并计算出最大值 TimeStamp 。

First, we create a summary table which calculates the last TimeStamp for each ID value. To do this, we use the SUMMARIZE function on a filtered table where we only consider dates from the current day or earlier, group by ID, and calculated the max TimeStamp.

一旦我们有了最大值对于当日每个 ID 的时间戳记，我们可以查看 Status 的状态

Once we have the maximum TimeStamp per ID for the current day, we can look up what the Status is on that day and add that as a column to the summary table.

一旦我们知道每个对象的最新状态，就将其添加到摘要表中。当天的 ID ，我们只需要总结状态为的状态 B 并忽略其他内容。

Once we know the most recent Status for each ID for the current day, we just need to sum up the ones where that Status is "B" and ignore the other ones.

阅读度量可能更容易如果我们将其分解为多个步骤。逻辑与以前相同，只是使用变量更清晰。

It may be easier to read the measure if we break it up into steps. Here's the same logic as before, but using variables for more clarity.

CountB = 
    VAR CurrDay = MAX(Inventory[TimeStamp])
    VAR Summary = SUMMARIZE(
                      FILTER(
                          ALL(Inventory),
                          Inventory[TimeStamp] <= CurrDay
                      ),
                      Inventory[ID],
                      "LastTimeStamp",
                      MAX(Inventory[TimeStamp])
                  )
    VAR LookupStatus = ADDCOLUMNS(
                           Summary,
                           "Status",
                           LOOKUPVALUE(Inventory[Status],
                               Inventory[ID], Inventory[ID],
                               Inventory[TimeStamp], [LastTimeStamp]
                           )
                       )
    RETURN SUMX(LookupStatus, IF([Status] = "B", 1, 0))

这篇关于如何使用Power BI DAX从移动表计算每天的库存？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！