How to calculate inventory per day from movement table with Power BI DAX?

Question

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.

Example of the source table:

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

Example of the output table:

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

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

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.

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

Thanks a lot! Gergő

Answer 1

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

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.

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.

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