Forum Discussion
Post ProdigyStock Turnover
Microsoft EmployeeHi ElliotP,
According to your description, you want to get the difference between the date when sold the first beer and the date when sold the last beer (the 24th beer), right? Then what does " how long it takes on average across a time period" mean? The average date range about selling all kinds of beers?
From the pbix file you rprovided, I am confused about so much tables and data records. Would you please create some sample data, only to list those tables and columns , and thier relationsips that will be used in this scenario so that I can reproduce your requirement?
Thanks,
Yuliana Gu
Post ProdigyI am attempting to create the level of stock turnover without an inventory management system so we have to take a slightly different approach.
For example; Assuming there are 24 beers in a case and the venue purchases the beer by the case. I would like to work out the time difference between when the first beer was sold and the 24th beer was sold. I would like to be able to work out this value continuously as so over a period of a time you could average the resultant time values to work out how long it took to sell a case of beer.
My thoughts were to use a rolling cumulative measure and every 24 beers for example (1-24, 25-49,etc) it makes a note of the difference in time.
In my linked pbix it has a lot of things. The relevant Columns in the data table, Itemdetails$dogfood are [date], [item], [qty]. The date table is the 'extendedcalendarenglish'.
What you are asking for is an inventory tracking system, but using an assumption of FIFO to identify lots and track their duration in the system. To make the calculation, I would divide the cumulative qty by the package size (i.e. 24 beers, 8 hot dogs, etc.) and filter where the remainder is 0. The divide by column is your lot code that you can join (with product) the sales and receipts tables together with.
I've attempted something similar in the past for component production in a factory, but in my opinion it took too many resources and I'm not sure the results meant much to the audience (myself included) and while it was nice to look at I don't think it was very actionable data. The reason I went that route is component jobs were opened and closed within the same week so ending balance data was not useful...in your case it doesn't sound like you have that problem in which case I would stick to textbook method for Stock Turnover. You can always convert it to days on hand instead of inventory turns.
