cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Help --- Calculate the ending inventory

Hey there,

 

I'm very new to BI, so i might ask something very weird. 😞  I want to calculate the ending inventory from this Sept to the Dec. I'm now having a one table  including work order (production), on hand inventory and demands. Given that, I calculated the inventory movement at first (work order Qty + On hand Inv - Total Demands). and then applied the cumulative concept to get my ending inventory. But because demands data is from this Jan to this Sept, but work order and on hand inventory is only from this Sept to Dec. So, the rolling inventory ( I named as inventory movement) is not right. How should I only implement the calculation for Sept to Dec. 

 

By the way, would you help check whether my calculation dax is right or not? Thanks!

 

Ending Inv = CALCULATE(CALCULATE([Movement],FILTER(ALLSELECTED(FactTable),FactTable[Date]<=MAX(FactTable[Date]))),FILTER(all(FactTable),COUNTROWS(FILTER(FactTable,EARLIER(FactTable[SKU])=FactTable[SKU]))))

 

 

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted

Hi @naoyixue1 

I think you need cumulative the work order before current month. Your Work order column may be a Measure, however, I don't know its calculate logic.

You may try this measure:

Cumulative Work Order = calculate(Sum([Work Order]),Filter('Calendar','Calendar'[Date]<= Max('Calendar'[Date])))

If this reply still couldn't help you solve your problem, could you show me the calculate logic of your work order measure.

And I need to know your data model to understand your measure, you can show me a sample data model like the one you are dealing with. Or you can share your pbix file with me by your Onedrive for Business.

 

Best Regards,

Rico Zhou

 

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

Highlighted

Hi @naoyixue1 

Could you kindly accept the helpful reply as a solution? More people will benefit from it. 

Best Regards,

Rico Zhou

View solution in original post

7 REPLIES 7
Highlighted
Super User IV
Super User IV

@naoyixue1 , I term of measure you can have a measure with Initial on hand the cumulative incoming and outgoing

 

new measure =

[Intial On Hand] + CALCULATE(SUM(Table[work order Qty]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Demand]),filter(date,date[date] <=maxx(date,date[date])))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted

Thanks! I'm wondering that whether I should limit the calculation of cumulative demands only from Sept to this Dec, otherwise, those previous demands will eat out Sept on hand inventory and work orders. If that is case, how can i do to narrow the date of demands from the beginning of the year to only the beginning of Sept? Thanks!

Highlighted

@naoyixue1 ,

You can use YTD with year end date

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"9/30"))

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"8/31")) // Give year end date.

 

Also, prefer date calendar

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions

Appreciate your Kudos.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted

Hey there,

 

Thanks for yall help. But i actually have the same issues again. See below. I applied the cumculative concenpt to calculate the work order, but i don't get the cumulative result. For example, my work order qty is 3000 units under SKU 10346 in Sept, and the Oct work order is 3001, so the cumulative Oct work order should be 6001 (3000 + 3001). Do you know why? Thanks!

 

naoyixue1_0-1600718561269.png

 

Highlighted

Hi @naoyixue1 

I think you need cumulative the work order before current month. Your Work order column may be a Measure, however, I don't know its calculate logic.

You may try this measure:

Cumulative Work Order = calculate(Sum([Work Order]),Filter('Calendar','Calendar'[Date]<= Max('Calendar'[Date])))

If this reply still couldn't help you solve your problem, could you show me the calculate logic of your work order measure.

And I need to know your data model to understand your measure, you can show me a sample data model like the one you are dealing with. Or you can share your pbix file with me by your Onedrive for Business.

 

Best Regards,

Rico Zhou

 

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

Highlighted

@RicoZhou Thanks Rico. I sovled my problem. As you metioned, I also did cumulative work order QTY , then i solved my problem. Thanks a lot!

Highlighted

Hi @naoyixue1 

Could you kindly accept the helpful reply as a solution? More people will benefit from it. 

Best Regards,

Rico Zhou

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors