Forum Discussion
Running total intelligence to sum data over time periods
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.
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 knowHi 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.
2 Replies
- Gabry
Super User
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
Community Champion
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.