Sorry, but I’m not allowed to share the pbi file. However:
Se enclosed DataModel.png. Data is loaded directly from an ERP system. I have created a consolidated table “Kommande Lagertransaktioner” (approx. Planned warehouse transactions, lower right corner) which holds data for remaining Customer order lines (PUPROHUV and PUPROKRO) and remaining Purchase order lines (PUIKOHUV and PUIKOKRO). The consolidated table share fields for Customer/Supplier, Order Number, Quantity and Amount with the field Source as differentiator. The table are connected to the DatesTable and an Item table (PULAGER). The Item Table also holds the current outstanding balance (Qty) for each item.
I want to create a report that shows cumulated total over time (se enclosed Report_Stock_Qty.png):
I have managed to create the cumulated total, but I want to add an opening balance for each item, stored in the Items table, as a starting value. Let say Item (Artikelkod) 211-0007 has a stock quantity of 1000 pcs as per today, then the Running total as per 2020-06-11 will be 1200 pcs and the running total 2020-08-21 will be 50.
The running total measure is defined as:
Running Total = CALCULATE(
Kommande_Lagertransaktioner[Datum] <= MAX(Kommande_Lagertransaktioner[Datum])
Where measure “Rest_Antal_tot_per_dag” is defined as : “[ Purchase Order] – [CustOrder]”
So, my remaining question is, How do I add a value for the opening balance of each Item
Thanks in advance