Hi,
I try to create a measure to cumulate Stock balance figures over time.
My first problem is that I want the cumulation to start from zero, now it seems the starting shows the accumulated total to that point.
My Second problem is that I want to add outgoing stock balance by start-date to the cumulation
My current expression is:
if(COUNTROWS(Stocktransactions) >0;
CALCULATE(
[Rest_Antal_tot_per_dag];
FILTER(
All(DatesTable[Date]);
DatesTable[Date] <= MAX((DatesTable[Date]))
)) ;
BLANK()
)
which show total by start date
Hi @robsmi, we’ve noticed that no response has been received from you since the 15th of May. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!
Here comes the enclosed file again. My problem is the “Row net accumulate” expression. First of all I want it to start counting from zero, second I want to add a value for the items opening stock balance
@robsmi please provide a sample pbi file with data model so we fully capture what you are trying to achieve. At the moment its very difficult to understand what you are trying to work out without sample data and data model.
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(
[Rest_Antal_tot_per_dag],
FILTER(
ALLSELECTED(Kommande_Lagertransaktioner[Datum]),
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
Hi @robsmi, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!
