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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
naoyixue1
Post Patron
Post Patron

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

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

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
amitchandak
Super User
Super User

@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])))

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!

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

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

 

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. 

 

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

Hi @naoyixue1 

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

Best Regards,

Rico Zhou

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.