Log In
Register
Home
March 3, 2021
Offline
Dear,
I wanted to create days on hand (DOH), but when there is no demand, I get an error.
For example.
Stock today 500
Week 39 no demand.
Week 40 October 5th 400 pcs
For me Days on hand are ((Stock+Receipts)/Demand * working days) - working days.
I should expect that week 38 (this week) I should see DOH = 8 days.
Week 39 = 7 days.
But for those week, I get "# NUM!".
The formula I used are:
StockReceipts/Requirements:=([Total Stock]+[Total Receipts])/[Total Requirements]
DOH:=CALCULATE([StockReceipts/Requirements];FILTER(ALLSELECTED('Date'[Date]);ISONORAFTER('Date'[Date];MAX('Date'[Date]);DESC)))
Can somebody help me with the right formula , so that the "# NUM!" are replaced the the right value?
I added some example. I created the formula in power pivot.
July 16, 2010
Offline
Hi Johan,
Please upload the file again. Be sure to click the 'Start Now' button and wait for the grey check mark beside the file size to indicate it's finished uploading before submitting your reply.
Mynda
March 3, 2021
Offline
Hey Mynda,
Thanks for the remark. File added.
Johan
July 16, 2010
Offline
Hi Johan,
Please explain how you get the working days value? It is nowhere in your model. Please also show an example where you calculated DOH for week 39 using the values in your model, so we can trace it back and understand where all the figures come from.
Mynda
March 3, 2021
Offline
Hey Mynda,
I added a print screen of what I wanted to calculate. In SAP, you can see how many days you have stock.
Regarding to me this is calculate (DOH = ((Stock+ Receipts)/Recequirements) * working days - working days.
This I wanted to do in excel, via Power pivot. In my excel file, you can go to power pivot.
But I don't know how to do this, taking into account that I have stock, but no demand current week. And do I have to add in the calendar the working days? For example when it's Saterday or Sunday = 0 and other days are 1?
And is it then possible to create a formula like below? And what in case I don't have demand, but only week after as I mentioned?
It should 
1 Guest(s)
