Forum Discussion
Post PatronHelp --- Calculate the ending inventory
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.
Hi naoyixue1
Could you kindly accept the helpful reply as a solution? More people will benefit from it.
Best Regards,
Rico Zhou
Super Usernaoyixue1 , 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])))
Post PatronThanks! 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!
- amitchandak
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.- naoyixue1
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!
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.