Number of Days stock on hand

[vitalities]

Hello everyone,

i have the following data set at SKU-location level: 1)period  2)stock quantities in cases 3) 8 columns of weekly forecast in CS (week 0 to week 7).

Based on that, i need to calculate days on hand / weeks on hand at each period at any selected level of product hierarchy (SKU or higher level) and any aggregation of storage locations

Please help me with the ideas on how it  can be delivered. Thanks a lot in advance

[v-yuezhe-msft · Accepted solution]

@vitalities,

Please check if DAX in the attached PBIX file returns your expected result. 

Regards,
Lydia

[v-yuezhe-msft]

@vitalities,

Please follow the instructions in the thread below to provide sample data of your table here and post expected result.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Regards,
Lydia

[vitalities]

Days on hand should be calculated based onstock and forecast , in case stock is more than 8 weeks of forecast, it should be considered as 57

Here it is. Stock and forecast are in same units. I need a measure to calculate how many days of forecast will be covered with stock we have

[vitalities]

Expected result