Days of Supply

[Anonymous]

Hi I have the data in the following format and I want to calculate a measure for future days of supply based on the sales demand of future months. Basically I want to see for how many days the closing stock at end of each month is enough to cover the future sales (at different hierarchical levels -product/brand/market etc)
I m at a total loss at how to create a measure which could be achieve this. 

Some info about the data:

The closing stocks and sales information is in the same column. 

There are different markets and products and brands.

The value and volume data is in the same column as well.

date (mm-YYYY)	Cycle	Market	Prod Code	Data Type	Amount	Category	Month	Year	Quarter	Brand Name
12-2019	Mar'20	Australia	10201	Sales	142	Volume	12	2019	Q4	XYZ
12-2019	Mar'20	China	1035235	Sales	14889	Value	12	2019	Q4	ABC
01-2020	Mar'20	Turkey	1052348	Sales	87	Volume	01	2020	Q1	MMM
12-2019	Mar'20	Turkey	1062345	Stock	87	Value	12	2019	Q4	XXX

I found a related query on another forum however, that solution is not working for my data type.

Please please please help me on this! the progress of my whole project is stuck because of this 😞

[Greg_Deckler]

I actually wrote a Days of Supply quick measure once and a better/improved version is in my book.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Days-of-Supply/m-p/635656?search-action-id=1...

[Anonymous]

Hi Greg,

Thank you for your response. 

I actually did earlier try the formula in the link that you have provided. But it isn't working for me. I tried editing it as per my data but I get blank results. 

[Anonymous]

hi @Greg_Deckler ,

can you kindly look at the sample data: sample data 

I tried the formula again and I m getting the supply cover for one location only. For other one I m getting blanks. 

Also, another problem is that i have  monthly ending stocks.

So for instance, the following would mean that 394k is the ending stock so I should not be taking into account the sales data of that month to calculate the cover. I need to start calculating the cover from Feb'20 onwards. Any idea how to tweak the formula for that?

Market	Month-year	GMM	Sales data	Stock
Location 1	01-2020	Prod A	274963	394351.4

And since I have week numbers based on the year, at the end of the year I get a blank for days cover.