I am trying to create a running stock total for a number of products over time. I am using two data values (qty), the first is associated with a date (Incoming deliveries), the second is a current stock level and is therefore not associated with a date.
After building a matrix to display products in the rows and date/weeks in columns, I have attempted to create a calculated measure to show the running total combining current stock level and incoming deliveries. The measure is a simple calculate expression, and the outcome is correct, however, the output values only appear in my matrix under the week columns where there are incoming deliveries.
Is there a way that I can show the stock level under all weeks, and not just those with incoming deliveries?
Thank you for your response. I have the two sets below (simplified).
I have created a calculated column to withdraw the week number from the bottom table (incoming), and there is a relationship between the incoming table and stock table.
I am laying out as below:
As you can see, I have my product as rows, week number as columns. I have created measures for the Incoming Qty and Stock Qty, and the simple calculation I have used is: Rolling Total = [Stock Qty] + [Incoming Qty].
I'd like to see the blank weeks filled in, but I can't seem to achieve it having tried various calculations and seeking other advice on the forum.
Thank you for detailing this perfectly - I've tried and tested for all products that I have data for (100+) and it gives me a running total which I couldn't achieve before.
If I filter from a certain date, say Week 33 for instance, would it only take into account future data points?
I also have a third table giving descriptions for each product code, which is linked to both data tables. Whenever I have tried to include the description in the table it either crashes or just does not work - is this perhaps a memory issue due to the vast number of calculations that BI needs to do?