Remaining Inventory after weekly sales

[abtm]

Hi,

I am running short of ideas to get the desired table/Matrix.

I have a static table that contains product ids(unique) and static inventory values in units. The second table has sales data with product ids, shipped dates, and shipped units—finally, a date table with the week number.

I am trying to visualize this in a matrix, where it shows me weekly sales in units and deduct that number from the static inventory qty, thus showing the remaining stock. Also, there is a grand total column of all units sold and remaining stock for that particular product. 

		week 30	week 31	Grand total
prod id	static inv qty	units sold	Remaining stock	units sold	Remaining stock	units sold	Remaining stock
A	100	8	92	7	85	15	85
B	200	5	195	20	175	25	175
C	300	6	294	6	288	12	288

I tried using measures like:

Remaining Stock =

var _currdate = MAX('Date Table'[Date].[Date])

return

    CALCULATE((SUM(Static[Inventory])-SUM(Sales[Quantity])),

        FILTER(

            ALLSELECTED('Date Table'[Date].[Date]),

            ISONORAFTER('Date Table'[Date].[Date], _currdate, DESC)

        )

    )

I know I am not accounting for the value calculated for week 30 to be used for week 31. The above measure considers the static inv quantity for every week and deducts weekly units from that number. I end up seeing the values like:

		week 30	week 31	Grand total
prod id	static inv qty	units sold	Remaining stock	units sold	Remaining stock	units sold	Remaining stock
A	100	8	92	7	93	15	85
B	200	5	195	20	180	25	175
C	300	6	294	6	294	12	288

Even though my grand total makes sense, I want to show the weekly depletion.

How do I calculate and create a measure so that the week 30s Remaining stock is used to deduct value from week 31 units sold?

Kindly help me out.
Thank you very much.

[v-tangjie-msft · Accepted solution]

Hi @abtm ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a calculated column.

Weeknum = WEEKNUM('Sales'[ship date],2)

(2)We can create measures.

Cumulative Sales = 
CALCULATE(SUM('Sales'[shipped units]),FILTER(ALL('Sales'),'Sales'[Weeknum]<=MAX('Sales'[Weeknum]) && 'Sales'[prod id]=MAX('Sales'[prod id])))

Remaining Stock = SUM('Static inv'[static inv qty]) -[Cumulative Sales]

(4) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[abtm]

Apologies. I am not sure how the table got messed up and throws me an HTML error if I try to edit it.

expected table:

current table: