Hi,
I poseted something similar before but didnt get a solution that worked.
I am trying to get a predicted running stock total. I have 3 dimentions [Current Stock], [Useage], [Purchases]
The calulation for month 1 is ([Current Stock] + [purchases (for month 1]) - [usage (for month 1)]
Then the following month the calculation will use the previous months output and then calculate the current months purchases - the current month usage. If there is no data for a pactiular month then usage or purchases should be classes as 0 and the stock would roll over to the next month. The issue to the previous solution was when there was no data for some months the calculation would break
Example data and example outputs are below:
Current Stock;
| Material | Stock on Hand |
| A | 100 |
| B | 500 |
| C | 1000 |
Purchases
| Material | PurchDate | Quantity |
| A | 01/01/2023 | 10 |
| A | 01/02/2023 | 20 |
| A | 01/04/2023 | 30 |
| B | 01/01/2023 | 10 |
| B | 01/02/2023 | 20 |
| B | 01/04/2023 | 15 |
Usage
| Material | UseageDate | Quantity |
| A | 01/01/2023 | 50 |
| A | 01/02/2023 | 10 |
| A | 01/04/2023 | 20 |
| B | 01/01/2023 | 10 |
| B | 01/02/2023 | 30 |
| B | 01/04/2023 | 40 |
What i want to do is create a table within power bi that outputs a visual with a running total calculated like the below. I have a calendar table already created:
| Material | 01/01/2023 | 01/02/2023 | 01/03/2023 | 01/04/2023 |
| A | 60 | 70 | 70 | 80 |
| B | 410 | 420 | 420 | 395 |
(Stock on hand + Purch) - Usage(Previous month LoB + Purchases) - usage
hope you can help
Many thanks in advance
Josh
