Weeks on Hand Calculation

[Staceigh]

Hello everyone!! I am trying to create an equation for determing Weeks on Hand that mimics the one we currently use in Excel. 

Here is the current excel formula: 

=IF(ISERROR(IF(K362<SUM(L348:M348),K362/(SUM(L348:M348)/2),IF(K362<SUM(L348:N348),K362/(SUM(L348:N348)/3),IF(K362<SUM(L348:O348),K362/(SUM(L348:O348)/4),IF(K362<SUM(L348:P348),K362/(SUM(L348:P348)/5),IF(K362<SUM(L348:Q348),K362/(SUM(L348:Q348)/6),IF(K362<SUM(L348:R348),K362/(SUM(L348:R348)/7),IF(K362<SUM(L348:S348),K362/(SUM(L348:S348)/8),K362/(SUM(L348:T348)/9))))))))),IF(K362>0,999,-1),IF(K362<SUM(L348:M348),K362/(SUM(L348:M348)/2),IF(K362<SUM(L348:N348),K362/(SUM(L348:N348)/3),IF(K362<SUM(L348:O348),K362/(SUM(L348:O348)/4),IF(K362<SUM(L348:P348),K362/(SUM(L348:P348)/5),IF(K362<SUM(L348:Q348),K362/(SUM(L348:Q348)/6),IF(K362<SUM(L348:R348),K362/(SUM(L348:R348)/7),IF(K362<SUM(L348:S348),K362/(SUM(L348:S348)/8),K362/(SUM(L348:T348)/9)))))))))

Where K362 is Projected Inventory Bal (CAS) of current week and row 348 is the Demand and the various combindations of this are just 2 weeks of demand, 3 weeks of demand, 4 weeks of demand, etc. Each column represents another week as we plan in weekly buckets. 

Equation is essentially, if Projected Inventory Balance is less than the sum of the next two weeks of demand, then divide Projected Inventory Balance by the sum of the next two weeks of demand divided by two(for two weeks of demand) if not procced to the next.

Next would be, if Projected Inventory Balance is less than the sum of the next three weeks of demand, then divide Projected Inventory Balance by the sum of the next three weeks of demand divided by three(for three weeks of demand) if not procced to the next.

Etc, until you get to 9 weeks on hand in which case it's either going to be -1 or 999 if it doesnt fall into either of the buckets above because either its too big or small (or theres an error because there is no demand or inventory). 

I have the following information in my PowerBI Dashboard: Material Number, Key Figures for each week which include: Stock on Hand (CAS), Demand (CAS), In-House Production Qty (CAS), TPM Production Qty (CAS), Expected Release Qty (CAS), Projected Inventory Bal (CAS), and then we use the above calculation to come up with weeks on hand. I also have the weekly dates listed in the columns as the monday of each week but also did the weeknumber calculation to add in weeks numbers. I also created a calendar table. 

I am working on a manipulation dashboard and need to be able to manipulate the inputs to see changes to outcome and there need to recreate my formula. 

Any Assistance would be MUCH appreciated this is killing me trying to figure out how to mimic this outside of excel.

[amitchandak]

@Staceigh ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

if you are looking for days of inventory

https://www.youtube.com/watch?v=Wr4o5uneFxc

[Staceigh]

I am not quite sure how to attach a PBIX file. 

Here is a sample of data upload for PBI:

Here is a sample of current manual file for making production adjusts in Excel:

Here is what the data looks like that is uploaded in the file:

Here is the sample table in PBIX:

[Anonymous]

Hi @Staceigh ,

It makes it easier to give you a solution.

1. Sample (dummy dataset) data as text, use the table tool in the editing bar.
2. Expected output from sample data.
3. Explanation in words of how to get from 1. to 2.

How to provide sample data

How to Get Your Question Answered Quickly

Best Regards,
Community Support Team_Gao

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[Staceigh]

Thank you for the above. I was confused how others could upload a file but I couldnt. I have attached link to google drive that has excel and PBIX files. 

Excel:

https://drive.google.com/file/d/1Rcj_T42Wid1lyk3f3_sWS0eMC14LVxXx/view?usp=sharing

PBIX:

https://drive.google.com/file/d/16zTm9_GLF2vGvzgzMV6vAlOFRoP6dh1v/view?usp=sharing

[Staceigh]

If you cant access my files above let me know.