Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
abtm
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 30week 31Grand total
prod idstatic inv qtyunits soldRemaining stockunits soldRemaining stockunits soldRemaining stock
A1008927851585
B20051952017525175
C3006294628812288

 

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 30week 31Grand total
prod idstatic inv qtyunits soldRemaining stockunits soldRemaining stockunits soldRemaining stock
A1008927931585
B20051952018025175
C3006294629412288

 

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
v-tangjie-msft
Community Support
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)

vtangjiemsft_0-1691736031827.png

(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.

vtangjiemsft_1-1691736100820.png

 

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. 

View solution in original post

2 REPLIES 2
v-tangjie-msft
Community Support
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)

vtangjiemsft_0-1691736031827.png

(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.

vtangjiemsft_1-1691736100820.png

 

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
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:

abtm_0-1691605767416.png

 

current table:

abtm_1-1691605800041.png

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.