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.
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
Hey Mynda,
Thanks for the remark. File added.
Johan
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
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 
"And do I have to add in the calendar the working days?", yes, that is the missing piece of the puzzle. It's not clear what this represents e.g. working days until the end of the month, week, year?
