title: How to calculate inventory per day from movement table with Power BI DAX?
body: <p>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 <strong>number of inventory at a certain day in status 'B'</strong>. 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.</p>
  
  <p>Example of the source table:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Example of the output table:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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: </p>
  
  <pre><code>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
  </code></pre>
  
  <p>Then I would plug the above data into the Date context and count the number of IDs from the above table, where the "<em>TimeStamp of 'B' breached</em>" value is smaller AND the "<em>TimeStamp of next status breach</em>" value is greater than the certain date.</p>
  
  <p>Unfortunately I am not sure how to plug this logic into DAX syntax, hence any recommendations would be appreciated.</p>
  
  <p>Thanks a lot!
  Gergő</p>