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ő