Running total intelligence to sum data over time periods

[NordicS3130]

Hello,  I have a problem and I am not sure how to begin.  I have a table which has items that I purchased and then sold at a later date.  I would like to use this table to create another table which shows the inventory levels (number of units of each item) that I have on the specific date.  It would take into account the day that the items are purchased, group them by the item name, and have a running total over a period of time.  Ideally the outcome in a new table would look something like the second table below.  Does anyone have a suggestion of how to set this p in power bi?  Thank you very much for your help and suggestions.

[Gabry · Accepted solution]

Hello @NordicS3130 ,
you first need to unpivot your source table, then create a table with dates and use calculated column to count the stock per item.

I made a sample pbix with sofa calculation, you can take a look, let me know

[some_bih · Accepted solution]

Hi @NordicS3130 I created simple file from your column names logic and my data (amounts in measures in single, same currency). I created couple of measures with DAX Calendar table and relationships.

Running Total measure is crucial for running total as dates are different for purchased vs cost. As there are two date columns one column should be used with USERELATIONSHIP in measure

Please not I did not take care of logic that purchase should be lower than cost and vice versa, it is just example.

See enclosed file for details.

[Gabry · Accepted solution]

Hello @NordicS3130 ,
you first need to unpivot your source table, then create a table with dates and use calculated column to count the stock per item.

I made a sample pbix with sofa calculation, you can take a look, let me know