Calculate Projected Inventory for next 12 months

Question

Hi All,&nbsp;I am new in the power bi world and hoping someone can help me.&nbsp;I've been trying to calculate a Projected Inventory for next 12 months for the last 2 weeks without any luck&nbsp;:smileysad:Data is based on input from the MRP (D365 - Supply Schedule).&nbsp;I am trying to replicate the same concept in D365 – Supply Schedule where users can view at high level and detail level.&nbsp;Key Points;1. Previous date (01/01/1900) will be used to determine yesterday opening, net and closing qty2. Opening Qty = sum(On hand)3. Net Qty =Qty Receipt + Qty Issue&nbsp;4. Closing Qty = Opening Qty + Net Qty. This will be opening qty for next month regardless if there is transaction on that particular month.&nbsp;5. If there is no net qty for particular month, opening and closing will be the same as per closing value of last month.6. Need to calculate extended cost for point 2,3,4.7. I only provided a sample of data. I am handling more than 250K to 500K data&nbsp;DAX Calculation&nbsp; &nbsp; &nbsp; Cal Opening Qty = sum(Requirement[On Hand]) +&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(sum(Requirement[On Hand]) + sum(Requirement[Net Qty]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FILTER(ALLEXCEPT(Requirement,Requirement[ItemIDKey],Requirement[dataAreaId] ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Requirement[Month] &lt; MAX(Requirement[Month])))&nbsp; &nbsp; &nbsp; Net Qty = sum(Requirement[net qty)&nbsp;Cal Closing Qty = sum(Requirement[On Hand]) +&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(sum(Requirement[On Hand]) + sum(Requirement[Net Qty]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FILTER(ALLEXCEPT(Requirement,Requirement[ItemIDKey],Requirement[dataAreaId] ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Requirement[Month] &lt; MAX(Requirement[Month])))&nbsp; &nbsp; &nbsp; Extended Cost calculates by multyply above with costOutcomes;Issues;1. The opening qty does not carry over to next month due to no transactions (highlighted in yellow)&nbsp; &nbsp; If there is no net qty, the opening and closing qty should be the same as per closing value of last month.2. Opening, net and Closing value are calculated in-correctly (wrong). The calculation for Qty seems OK3. Total value is incorrect.&nbsp;The value should beI wanted to create a summary table by1. AU/NZ2. Vendor3. Item level&nbsp;Can someone please help me especially in DAX formula?&nbsp;CheersSF

v-lili6-msft · Answer

hi,&nbsp;Anonymous&nbsp;
It seems that the link&nbsp;doesn't work, please check it
That didn't work

We're sorry, but you can't be found in the nhp365-my.sharepoint.com directory. Please try again later, while we try to automatically fix this for you.

&nbsp;
Best Regards,
Lin

anonymous · Answer

Hi Lin (@lin),&nbsp;It seems OK for me to downloadload the pbix but saying that i might have access to my one drive. I believe i need to give permission to my folder or do you have alternative that i could send the file?&nbsp;Cheers&nbsp;SF&nbsp;

anonymous · Answer

Hi Lin (@v-lili6-msft)&nbsp;Try this link;Pbix - Projected Inventory (Sample Data)Please let me kn ow if it does not work.&nbsp;ThanksSF

v-lili6-msft · Answer

hi,&nbsp;Anonymous&nbsp;
1. You could try this formula as a new logic
New Cal Closing Qty = var _predate= CALCULATE(MAX(Requirement[Month]),FILTER(ALLEXCEPT(Requirement,Requirement[ItemIDKey],Requirement[dataAreaId]),Requirement[Month]&lt;MAX('Calendar'[Date]))) return
IF(ISBLANK(SUM(Requirement[Net Qty])), CALCULATE([Cal Closing Qty],FILTER(ALLEXCEPT(Requirement,Requirement[ItemIDKey],Requirement[dataAreaId]),Requirement[Month]=_predate)),[Cal Closing Qty])
But this will calculate for each month in&nbsp;Calendar table,&nbsp;for example: 2019/07 2019/08 2019/09... you must filter it or add a filter for it
2. Qty and Value have the same logic in your measure, what wrong with it.
3.&nbsp;This looks like a measure totals problem.&nbsp; See this post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
&nbsp;
Best Regards,
Lin

anonymous · Answer

Hi&nbsp;v-lili6-msft&nbsp;Thanks for formula. I used the formula that you gave to calculate Opening and Closing Qty. The details areOutput:These formula seem working at item level (when i filtered only one item :).However the closing Qty does not add up correctly at Country Level.Manual calculationPrior April 2019; Closing Qty should be&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AU: 4,295,670 + 391,474 = 4,687,144 (Not 4,686,964).&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NZ: 433,465 + 38,818 = 472,283 (Not 472,282)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Total: 5,159,427 (Not 5,159,246)It's also happnening for next month and so onI went to the website that you provided (to calculate total) - Measure Total, Final Words.I tried different combinations yesterday and today but i have no luck.&nbsp;The output isI was so happy when i saw closing Qty (Cal Close 2) prior April 2019 is correct but it stopped there.April, May, and so on are in correct.Can you please help me regarding this matter?Many thanks in advance&nbsp;RegardsSF&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

Forum Discussion

Calculate Projected Inventory for next 12 months

7 Replies

Related content

Inventory and Stock balance calculation

Projecting inventory with current week as starting point

Projected Available Balance Dax Calculation Help

Calculate Projection

Help to create a inventory projection report