Analyzing PO Performance

[Anonymous5]

Hi all,

I’m new to the community and new to Power BI. I am truly excited about the capabilities of this program for our company.

My first attempt at using PBI will be to help our Procurement group streamline their reporting of PO performance and find ways of improvement.

I have access to the SQL data tables for all POs created in our procurement system. Each row of the table represents a single PO with all associated data. What id like to do first is look at the date stamps at each milestone of the PO to determine “cycle time”. That is, time needed to progress through all stages of the PO. These are: Requisition to RFQ to PO creation, PO receipt and finally PO delivery. Each of these is represented by a column in the table.

Can Power BI look at these POs over a year for example and calculate this average “cycle time” for all POs made and present this by quarter? I’m thinking horizontal stacked bar chart would do a good job here with each quarter on the Y- axis and “days” along the X-axis. The different portions of each bar would be “average days as Requisition, days as RFQ, days to PO receipt, etc”

Appreciate any help on this one

Rgds,
Rob

[Anonymous]

@Anonymous5 - Could you provide some sample data? 

[Anonymous5]

Hi Nate,

Here's a sample of my query. Each row represents a PO created in 2019. The milestones occur along the way from it's preceding RFQ creation and approval through PO creation to receipt to delivery. I'd like my visualization to gather up all POs for a given quarter, average out the time spent at each phase and produce a horizontal stacked bar with 4 segments representing the following:

RFQ Creation - RFQ Aproval (days required)

RFQ Approval - PO Fiscal Effective Date (days required)

PO Fiscal Effective - PO Receipt (days required)

PO Receipt - PO Delivery (days required)

Thanks and Regards,

Rob

[Anonymous]

@Anonymous5 - Could you copy and paste the text? It makes it easier for us to use.

Thanks!

Nathan

[Anonymous5]

Hi Nate,

Which text are you referring to? All I've done so far is merge the tables using Excel Power Query and removed all the non-relevant columns for the sake of displaying here.

Rob

[Anonymous]

Sorry, I mean the text from Excel, instead of a screenshot. Then I can copy and paste that as a source in Power BI.

[Anonymous5]

Nate,

Here you go. I've never posted an entire table before so let me know if it's not what you're after. Below is the first 30 rows of the table.

PO_NUMBERRFQ.CREATED_DATERFQ.APPROVED_DATEFISCAL_EFFECTIVE_DATELAST_RECEIPT_DATELAST_DELIVERY_DATE

1118979	12/20/2018	12/28/2018	1/1/2019	2/26/2019	3/24/2019
1118980	12/31/2018	12/31/2018	1/1/2019	1/16/2019	1/26/2019
1118989	12/31/2018	1/1/2019	1/1/2019	1/9/2019	1/19/2019
1119003	11/19/2018	1/2/2019	1/2/2019	1/23/2019	2/20/2019
1118878	12/12/2018	12/20/2018	1/2/2019	3/28/2019	6/17/2019
1118877	12/19/2018	12/19/2018	1/2/2019	1/17/2019	1/30/2019
1118990	12/26/2018	1/1/2019	1/2/2019	4/10/2019	4/28/2019
1118996	12/26/2018	12/31/2018	1/2/2019	2/14/2019	2/23/2019
1118997	1/2/2019	1/2/2019	1/2/2019	1/10/2019	1/16/2019
1119025	4/3/2018	12/30/2018	1/3/2019	1/29/2019	2/26/2019
1119012	10/4/2018	1/2/2019	1/3/2019	5/21/2019	5/21/2019
1119005	11/14/2018	1/2/2019	1/3/2019	4/1/2019	4/30/2019
1119022	12/17/2018	1/1/2019	1/3/2019	2/25/2019	10/9/2019
1119013	12/19/2018	1/2/2019	1/3/2019	1/22/2019	3/6/2019
1119014	12/20/2018	2/18/2019	1/3/2019
1119021	12/21/2018	1/1/2019	1/3/2019	1/18/2019	1/20/2019
1119029	12/24/2018	1/2/2019	1/3/2019	1/9/2019	1/15/2019
1119024	12/24/2018	1/2/2019	1/3/2019	1/11/2019	1/15/2019
1119030	12/24/2018	1/2/2019	1/3/2019	1/10/2019	1/16/2019
1119010	12/26/2018	1/2/2019	1/3/2019	1/9/2019	1/16/2019
1119023	12/28/2018	1/1/2019	1/3/2019	1/18/2019	1/20/2019
1118992	12/31/2018	1/2/2019	1/3/2019	1/31/2019	2/19/2019
1119006	12/31/2018	1/2/2019	1/3/2019	2/7/2019	2/21/2019
1119006	12/31/2018	1/2/2019	1/3/2019	2/7/2019	2/21/2019
1119028	12/31/2018	1/1/2019	1/3/2019	1/14/2019	1/23/2019
1119027	12/31/2018	1/1/2019	1/3/2019	1/10/2019	1/23/2019
1119027	12/31/2018	1/1/2019	1/3/2019	1/10/2019	1/23/2019
1119027	12/31/2018	1/1/2019	1/3/2019	1/10/2019	1/23/2019
1119019	12/31/2018	1/3/2019	1/3/2019	1/9/2019	1/28/2019

[Anonymous]

@Anonymous5  - You could use something like the attached pbix file. There are several steps to consider with this solution:

1. Create a Date table with an M (Power Query) script.
2. Use a lookup table to identify the previous event.
3. In the PO table:
   1. Unpivot the dates - this will allow you to easily compare values within a single measure.
   2. Find the next event date by looking up the previous event, and merging table with itself.
   3. Do the same for "days since create date".
   4. Calculate the number of days between event and previous event, and created date to event.
4. Create a parameters table to allow user to choose between analyzing
   1. Days from creation to event or days since previous event.
   2. Which event's date (for instance, quarter) is being analyzed - create, previous event, or event.
5. Create an Event lookup table, with the purpose of ordering the events.
6. Create relationships between the Date table and PO table, and between the Event table and the PO table.
7. Create measures which take into account the selections from the Parameters table.
8. Create the visualizations

Note: The parameters table could be eliminated and the measures simplified, if only a single type of analysis makes sense.

I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.