cancel
Showing results for
Did you mean:
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
Community Support

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

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.

Community Support

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

Best Regards,

Rico Zhou

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

Proud to be a Super User!

Frequent Visitor

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!

Super User IV

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 :

Proud to be a Super User!

Frequent Visitor

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!

Community Support

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

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.

Frequent Visitor

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

Community Support

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

Best Regards,

Rico Zhou

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

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