Has anyone found a way to analyze PO lead times? a.k.a. average lead time? Here’s my quandary; in our purchasing database I can calculate the lead time days between the order and receive dates, but I’m struggling to find a way I can build that into a measure resulting in the average lead time.
It would be a useful product enhancement to have different Rollup options for measures - e.g. AVG - which some other BI tools have.
Bit convoluted but could you do a calculated measure -
- create a measure for count of the POs
- create a measure for sum of the lead time days
- then calculated measure to work out the Average - Sum of Lead time Days/Count of POs?
I haven’t tested whether you could use default restrictions to hide the interim measures which aren’t useful and only show the user the calculated Average.
Might depend whether you have multiple rows per PO - for the count you might need trans/count measure depending on this.
Hey @dan.klob ,
I’d like to offer a possible solution for this. It’s not exactly what you’re looking for, but I think it could at least give you similar information.
First, like you, I currently calculate my “days to receive” per PO line. I have uploaded a manual CSV document into DB Designer cross-referencing each “day” amount (1, 2, 3 . . . 25 . . . 172, etc.) to different “Day”, “Week”, and “Month” buckets that I’ve added as Dimensions:
This gives us the ability to visualize lead times in a table. For example, here’s a table I have on a dashboard showing lead times for items received in the last 3 months (how long those lines took to come in), and it shows the % of lines received within different “Week” buckets:
You could do this with actual line counts, quantities, $, etc. instead of the % Share if you’d like. It really helps to visualize which buckets different vendors are falling into after I’ve Focused into a specific vendor. So, for example, I might be able to see that for Vendor X, only 3% of recent lines came in within a week of ordering them; whereas maybe 50% of lines took between 3-8 weeks to arrive, and maybe 20%+ took over half a year.
Hope this helps! Let me know if you have any questions.
Neil, thanks for the thoughts and suggestions. Your last sentence is exactly the issue I’m running up against. My purchasing/PO data goes down to the product level which makes it difficult to “roll up” the numbers. i.e. PO P12345 is listed in my database 3-times because there are three different line items. That means the lead time for all three products would get added together overinflating everything. I had the trans/count thought too (great minds) and tried it a few different ways but still couldn’t get the numbers to work out.
Different rollup options for measures could be a powerful enhancement. That’d get my vote.
2 Likes
Tim, very cool idea. Thanks for the suggestion. I can see how having the ability to see those “buckets” for individual vendors and product lists would be helpful. Odds are good I’ll end up going down this road at some point.
1 Like
Thought that might be the case, that makes it tricky. Rollup options would definitely be useful.
Anything I have like that I have had to revert to dealing with in the data source itself, either by having an additional summary data set at the PO level or by trying to return the leadtime only against the first product line or splitting it over all the lines.
Good luck, hope you can find a solution.
1 Like
Let me know if you have any questions if/when you try to implement! It’s been very helpful in having informed discussions with our vendors, and also from an inventory ordering planning perspective.
1 Like