Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am new to Power BI but able to manage around few times. I am struggling to create cumulative of the data that I have. and some on time %. My file contains the following screenshot below. My visualization would be line and clustered column chart graph with the secondary axis as line chart.
I want to create the following:
1) cumulative Planned date for each milestone by month
2) % ontime = Planned vs Actual
Would appreciate any help and my table name is 'Export Worksheet'
PROJ_ID | PACKAGE | PACKAGE_DESC | MILESTONE_SET | ORD_SEQ | MILESTONE | PLANNED_DATE | FORECASTED_DATE | ACTUAL_DATE | VARIANCE |
P300000 | P0001 | Pump | MILESTONE 1 | 10 | Package Initiation | 03-Oct-18 | 10-Oct-18 | 7 | |
P300000 | P0001 | Pump | MILESTONE 1 | 20 | Technical Specification Started | 12-Oct-18 | 19-Oct-18 | 7 | |
P300000 | P0001 | Pump | MILESTONE 1 | 30 | Coordination Review Started | 23-Oct-18 | 04-Oct-18 | 04-Oct-18 | -19 |
P300000 | P0001 | Pump | MILESTONE 1 | 40 | Technical Bid Doc and ATP Issued for Approval | 01-Nov-18 | 08-Oct-18 | -24 | |
P300000 | P0001 | Pump | MILESTONE 1 | 50 | Bidders List Approved | 12-Nov-18 | 17-Oct-18 | -26 | |
P300000 | P0001 | Pump | MILESTONE 1 | 60 | Advice to Procurement (ATP) Acknowledged | 21-Nov-18 | 07-Nov-18 | -14 | |
P300000 | P0001 | Pump | MILESTONE 1 | 70 | Bid Request Released | 30-Nov-18 | 30-Nov-18 | 0 | |
P300000 | P0001 | Pump | MILESTONE 1 | 80 | Bid Closed | 11-Dec-18 | 11-Dec-18 | 0 | |
P300000 | P0001 | Pump | MILESTONE 1 | 90 | Package Technical Analysis Completed | 20-Dec-18 | 20-Dec-18 | 0 | |
P300000 | P0001 | Pump | MILESTONE 1 | 100 | Package Recommendation to Award Issued for Approval | 02-Jan-19 | 02-Jan-19 | 0 | |
P300000 | P0001 | Pump | MILESTONE 1 | 110 | Package Recommendation to Award Approved by Client | 11-Jan-19 | 11-Jan-19 | 0 | |
P300000 | P0001 | Pump | MILESTONE 1 | 120 | Technical Document IFC / IFF Issued | 22-Jan-19 | 22-Jan-19 | 0 | |
P300000 | P0001 | Pump | MILESTONE 1 | 130 | Purchase Order / Contract Issued | 31-Jan-19 | 31-Jan-19 | 0 | |
P300000 | P0001 | Pump | MILESTONE 1 | 2000 | First Vendor Data Received | 20-Feb-19 | 20-Feb-19 | 0 | |
P300000 | P0001 | Pump | MILESTONE 1 | 2010 | First Delivery or Mobilization Start | 21-May-19 | 21-May-19 | 0 | |
P300000 | P0001 | Pump | MILESTONE 1 | 2020 | Last Delivery/Work Completed | 20-Jun-19 | 20-Jun-19 | 0 | |
P300000 | P0002 | Conveyors | MILESTONE 1 | 10 | Package Initiation | 19-Sep-18 | 01-Oct-18 | 01-Oct-18 | 12 |
P300000 | P0002 | Conveyors | MILESTONE 1 | 20 | Technical Specification Started | 15-Sep-18 | 10-Oct-18 | 25 | |
P300000 | P0002 | Conveyors | MILESTONE 1 | 30 | Coordination Review Started | 09-Oct-18 | 19-Oct-18 | 10 | |
P300000 | P0002 | Conveyors | MILESTONE 1 | 40 | Technical Bid Doc and ATP Issued for Approval | 18-Oct-18 | 30-Oct-18 | 12 | |
P300000 | P0002 | Conveyors | MILESTONE 1 | 50 | Bidders List Approved | 29-Oct-18 | 08-Nov-18 | 10 | |
P300000 | P0002 | Conveyors | MILESTONE 1 | 60 | Advice to Procurement (ATP) Acknowledged | 07-Nov-18 | 19-Nov-18 | 12 | |
P300000 | P0002 | Conveyors | MILESTONE 1 | 70 | Bid Request Released | 16-Nov-18 | 28-Nov-18 | 12 | |
P300000 | P0002 | Conveyors | MILESTONE 1 | 80 | Bid Closed | 27-Nov-18 | 07-Dec-18 | 10 | |
P300000 | P0002 | Conveyors | MILESTONE 1 | 90 | Package Technical Analysis Completed | 06-Dec-18 | 18-Dec-18 | 12 | |
P300000 | P0002 | Conveyors | MILESTONE 1 | 100 | Package Recommendation to Award Issued for Approval | 17-Dec-18 | 28-Dec-18 | 11 | |
P300000 | P0002 | Conveyors | MILESTONE 1 | 110 | Package Recommendation to Award Approved by Client | 27-Dec-18 | 09-Jan-19 | 13 | |
P300000 | P0002 | Conveyors | MILESTONE 1 | 120 | Technical Document IFC / IFF Issued | 08-Jan-19 | 18-Jan-19 | 10 | |
P300000 | P0002 | Conveyors | MILESTONE 1 | 130 | Purchase Order / Contract Issued | 17-Jan-19 | 29-Jan-19 | 12 | |
P300000 | P0002 | Conveyors | MILESTONE 1 | 2000 | First Vendor Data Received | 06-Feb-19 | 18-Feb-19 | 12 | |
P300000 | P0002 | Conveyors | MILESTONE 1 | 2010 | First Delivery or Mobilization Start | 07-May-19 | 19-May-19 | 12 | |
P300000 | P0002 | Conveyors | MILESTONE 1 | 2020 | Last Delivery/Work Completed | 06-Jun-19 | 18-Jun-19 | 12 | |
P300000 | P0004 | New Package | MILESTONE 1 | 10 | Package Initiation | 26-Apr-18 | 26-Apr-18 | 02-May-18 | 6 |
P300000 | P0004 | New Package | MILESTONE 1 | 20 | Technical Specification Started | 03-May-18 | 03-May-18 | 0 | |
P300000 | P0004 | New Package | MILESTONE 1 | 30 | Coordination Review Started | 10-May-18 | 10-May-18 | 03-May-18 | -7 |
P300000 | P0004 | New Package | MILESTONE 1 | 40 | Technical Bid Doc and ATP Issued for Approval | 31-May-18 | 31-May-18 | 0 | |
P300000 | P0004 | New Package | MILESTONE 1 | 50 | Bidders List Approved | 14-Jun-18 | 14-Jun-18 | 0 | |
P300000 | P0004 | New Package | MILESTONE 1 | 60 | Advice to Procurement (ATP) Acknowledged | 18-Jun-18 | 18-Jun-18 | 0 |
Hi @luqmaanr,
Can you share the expected result, please?
1. Which value will be on the Column, which value will be on the line?
2. Which value will be cumulated?
3. Please download the demo in the attachment. Is it the one?
Best Regards,
Dale
The issue still not resolved. I have attached example in my previous posting.
Hi Dale
Thanks for responding, much appreciated.
1) Planned, Forecast and Actual will be the column data, cumulative planned, cumulative forecast and cumulative actual will be line as secondary axis
2) Planned, forecast and actual will be cumulated
See attached photo as an example from the below table.
Bidders List Approved - PLAN | Bidders List Approved - FORECAST | Bidders List Approved - ACTUAL | Bidders List Approved **bleep** Planned | Bidders List Approved **bleep** Forecast | Bidders List Approved - **bleep** Actual | ||
Jan-18 | 1 | 1 | 1 | 0 | 0 | 0 | |
Feb-18 | 2 | 3 | 4 | 2 | 3 | 4 | |
Mar-18 | 11 | 6 | 6 | 13 | 9 | 10 | |
Apr-18 | 15 | 12 | 10 | 28 | 21 | 20 | |
May-18 | 5 | 5 | 5 | 33 | 26 | 25 | |
Jun-18 | 5 | 8 | 9 | 38 | 34 | 34 | |
Jul-18 | 5 | 4 | 5 | 43 | 38 | 39 | |
Aug-18 | 9 | 8 | 7 | 52 | 46 | 46 | |
Sep-18 | 11 | 13 | 14 | 63 | 59 | 60 | |
Oct-18 | 3 | 7 | 4 | 66 | 66 | ||
Nov-18 | 3 | 6 | 0 | 69 | 72 | ||
Dec-18 | 0 | 0 | 0 | 69 | 72 | ||
Jan-19 | 1 | 1 | 0 | 70 | 73 | ||
Feb-19 | 0 | 0 | 0 | 70 | 73 | ||
Mar-19 | 5 | 1 | 0 | 75 | 74 | ||
Apr-19 | 0 | 1 | 0 | 75 | 75 | ||
May-19 | 0 | 0 | 0 | 75 | 75 | ||
Jun-19 | 0 | 0 | 0 | ||||
Jul-19 | 0 | 0 | 0 | ||||
Aug-19 | 0 | 0 | 0 | ||||
Sep-19 | 0 | 0 | 0 | ||||
Oct-19 | 0 | 0 | 0 | ||||
Nov-19 | 0 | 0 | 0 | ||||
Dec-19 | 0 | 0 | 0 | ||||
Jan-20 | 0 | 0 | 0 | ||||
Feb-20 | 0 | 0 | 0 | ||||
Mar-20 | 0 | 0 | 0 |
Hi @luqmaanr,
The problem here is how we can get the data in your second post from the data in your first post. I can't find any connection.
Can you modify the demo I attached before?
Best Regards,
Dale
Hi Dale,
The data in my second post is from Excel, using the same format of the data from my first post. I was able to do it in Excel, but found Power BI can easily connect to application and we want to utilitze it.
In excel, I can write my formula to count (COUNTA) the Planned date <= today() for each month and same for forecast. But for actual no need, you can just COUNTA.
I want to achieve the following.
1) Planned, Forecast and Actual will be the column data, cumulative planned, cumulative forecast and cumulative actual will be line as secondary axis
2) Planned, forecast and actual will be cumulated
3) % ontime = Planned vs Actual - Planned is to count all the planned date <=today ().
I cannot attach excel file in this forum, please let me know if you need additional info.
Thanks Again.
Hi @luqmaanr,
I don't know which parts you already have and which parts you expect. Please download the demo in the attachment. Is it the solution?
Why does the **bleep** column start from 0?
Best Regards,
Dale
Hi Dale,
On the below graph How automatically would I stop projecting once they have reached their last data point for the cumulative data.
The cumulative line should stop on Dec 2018. Please help
Here's the formula:
thanks
Hi Dale
Thanks Again, this is the solution but it should read from the Export sheet table instead. When I am reading the cumulative from the export table it doesn't work.
The Planned vs Actual, what is the solution to not read the blank.
I am unable to add an attachment. The bleep start with 0 as the start of the project.
Thanks
Luqmaan
Hi Luqmaan,
How does the solution read from the Export sheet? I can't see any connections between these tables.
Best Regards,
Dale
Hi Dale
I think we had some misunderstanding here:
1) The real data is the export work sheet
2) the other screenshot (which is in my 11-23-2018 post) I sent you is an example of what I want to achieve with Export work sheet using Power BI. The screenshot was completed in excel.
Thanks
Luqmaan
Hi Luqmaan,
Yes, that's my assumption. You verified that. Now the question is how you will do to get the result without Power BI. Then I can do it in Power BI.
I have the sample file. But I don't know the logic from the real data to the result.
Best Regards,
Dale
hi Dale
1) I would count the number of Packages that are associated within that month for specific miletones (=COUNTIFS(POP!AR$4:AR$52,"<"&$AM7) - AM7 today's date
2) for the cumulative, I would take the total from previous month and add it with the actual month for e.g. if Jan 2018 I had 2 Packages completed and and in Feb 2018 I had 5 Packages completed, then my cumulative should be 7.
How could I add an attachment to this forum. Then I can send you the excel file.
thanks
Luqmaan