cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Regular Visitor

## Remaining Inventory after weekly sales

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.

1 ACCEPTED SOLUTION
Community Support

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.

2 REPLIES 2
Community Support

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.

Regular Visitor

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: