如何使用 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"状态的库存数量.逻辑是计算在某一天之前违反状态B"但在该天之前没有任何新状态的不同 ID 的数量.

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 数量，其中'B' 违反的时间戳"值较小且下一次违反状态的时间戳"值大于特定日期.

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.

非常感谢！格尔格

推荐答案

这有点棘手，但我们可以通过在度量中使用临时计算汇总表来做到这一点:

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
        )
    )

首先，我们创建一个汇总表，计算每个 ID 值的最后一个 TimeStamp.为此，我们在过滤表上使用 SUMMARIZE 函数，我们只考虑当前日期或更早的日期，按 ID 分组，并计算最大 时间戳.

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 的最大 TimeStamp，我们就可以查找当天的 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 的最新 Status，我们只需要总结那些 Status是 "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屋！