I've just posted a similar question but I did'n explained the problem very well, I apologize if I'm posting twice but I don't know how to cancel the previous post. I hope that this post would be clear.
I'm trying to build a report using DAX running total calculation to get stock level from data set imported from two DB which record all inbound and outbound movements.
I have two problems:
1) I don't know the DAX formula to sum all movements for data
2) The initial stock quantity is not imported from the data set, it's a known data but I don't know how to use it in the DAX
The sample data set are as following (the real data set are more complicated but the following tables simplify well, the only difference are that I have more columns that are not involved in this calculation, that row are thousands as we have many mnovements every day, and that date includes also hour/minute/second).
OUTBOUND_TABLE (data imported from the production DB)
| DATE | ITEM | QUANTITY |
02 November 2020 | BE01 | 3 |
| 02 November 2020 | BE01 | 2 |
| 03 November 2020 | CE03 | 4 |
| 06 November 2020 | BE01 | 3 |
| 06 November 2020 | CE03 | 2 |
| 12 November 2020 | ZA02 | 3 |
| 01 December 2020 | CE03 | 3 |
| 03 December 2020 | ZA02 | 2 |
INBOUND TABLE (data imported from the ERP DB)
| DATE | ITEM | QUANTITY |
01 November 2020 | BE01 | 30 |
| 06 November 2020 | ZA01 | 20 |
| 10 November 2020 | CE03 | 40 |
| 15 November 2020 | BE01 | 30 |
| 30 November 2020 | ZA02 | 20 |
| 30 November 2020 | ZA02 | 3 |
| 01 December 2020 | CE03 | 3 |
From the two data set we have all inventory movements (in & out) but we don't have the stock level at the beginning. This information is known and I'm trying to figure out how to use it in my model only one time as a starting point to calculate the correct quantity of stock for each product every day.
Let's assume that the stock level of starting point is:
DATA | ITEM | QUANTITY |
30 October 2020 | BE01 | 250 |
| 30 October 2020 | CE03 | 410 |
| 30 October 2020 | ZA01 | 180 |
| 30 October 2020 | ZA02 | 110 |
The desired output is a report that calculate the stock level for each item and every day, for example for item BE01 on the 1st November we have quantity = 250 as initial stock (this data is not imported from DB), +30 as inbound, -3 and -2 as outbound = 275 the stock level of item BE01
30 October 2020 | BE01 | 250 |
| 01 November 2020 | BE01 | 30 |
| 01 November 2020 | BE01 | -3 |
| 01 Novemebr 2020 | BE01 | -2 |
Setting the correct initial stock for each item as the data set imported are all inventory movements I could calculate the correct stock level per data and also forecast the consumption.
