Visualization in Power BI

[luqmaanr]

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

[v-jiascu-msft]

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

[luqmaanr]

The issue still not resolved. I have attached example in my previous posting.

[luqmaanr]

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

[v-jiascu-msft]

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

[luqmaanr]

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.

[v-jiascu-msft]

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

[luqmaanr]

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:

Forecast accumulated = CALCULATE(sum(Table1[Bidders List Approved - FORECAST]), FILTER(all('Calendar'[Date]), 'Calendar'[Date]<=max('Calendar'[Date])))

thanks

[luqmaanr]

[luqmaanr]

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

[v-jiascu-msft]

Hi Luqmaan,

How does the solution read from the Export sheet? I can't see any connections between these tables.

Best Regards,
Dale

[luqmaanr]

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

[v-jiascu-msft]

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

[luqmaanr]

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