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 month2) % ontime = Planned vs Actual Would appreciate any help and my table name is 'Export Worksheet' PROJ_IDPACKAGEPACKAGE_DESCMILESTONE_SETORD_SEQMILESTONEPLANNED_DATEFORECASTED_DATEACTUAL_DATEVARIANCEP300000P0001PumpMILESTONE 110Package Initiation03-Oct-1810-Oct-18 7P300000P0001PumpMILESTONE 120Technical Specification Started12-Oct-1819-Oct-18 7P300000P0001PumpMILESTONE 130Coordination Review Started23-Oct-1804-Oct-1804-Oct-18-19P300000P0001PumpMILESTONE 140Technical Bid Doc and ATP Issued for Approval01-Nov-1808-Oct-18 -24P300000P0001PumpMILESTONE 150Bidders List Approved12-Nov-1817-Oct-18 -26P300000P0001PumpMILESTONE 160Advice to Procurement (ATP) Acknowledged21-Nov-1807-Nov-18 -14P300000P0001PumpMILESTONE 170Bid Request Released30-Nov-1830-Nov-18 0P300000P0001PumpMILESTONE 180Bid Closed11-Dec-1811-Dec-18 0P300000P0001PumpMILESTONE 190Package Technical Analysis Completed20-Dec-1820-Dec-18 0P300000P0001PumpMILESTONE 1100Package Recommendation to Award Issued for Approval02-Jan-1902-Jan-19 0P300000P0001PumpMILESTONE 1110Package Recommendation to Award Approved by Client11-Jan-1911-Jan-19 0P300000P0001PumpMILESTONE 1120Technical Document IFC / IFF Issued22-Jan-1922-Jan-19 0P300000P0001PumpMILESTONE 1130Purchase Order / Contract Issued31-Jan-1931-Jan-19 0P300000P0001PumpMILESTONE 12000First Vendor Data Received20-Feb-1920-Feb-19 0P300000P0001PumpMILESTONE 12010First Delivery or Mobilization Start21-May-1921-May-19 0P300000P0001PumpMILESTONE 12020Last Delivery/Work Completed20-Jun-1920-Jun-19 0P300000P0002ConveyorsMILESTONE 110Package Initiation19-Sep-1801-Oct-1801-Oct-1812P300000P0002ConveyorsMILESTONE 120Technical Specification Started15-Sep-1810-Oct-18 25P300000P0002ConveyorsMILESTONE 130Coordination Review Started09-Oct-1819-Oct-18 10P300000P0002ConveyorsMILESTONE 140Technical Bid Doc and ATP Issued for Approval18-Oct-1830-Oct-18 12P300000P0002ConveyorsMILESTONE 150Bidders List Approved29-Oct-1808-Nov-18 10P300000P0002ConveyorsMILESTONE 160Advice to Procurement (ATP) Acknowledged07-Nov-1819-Nov-18 12P300000P0002ConveyorsMILESTONE 170Bid Request Released16-Nov-1828-Nov-18 12P300000P0002ConveyorsMILESTONE 180Bid Closed27-Nov-1807-Dec-18 10P300000P0002ConveyorsMILESTONE 190Package Technical Analysis Completed06-Dec-1818-Dec-18 12P300000P0002ConveyorsMILESTONE 1100Package Recommendation to Award Issued for Approval17-Dec-1828-Dec-18 11P300000P0002ConveyorsMILESTONE 1110Package Recommendation to Award Approved by Client27-Dec-1809-Jan-19 13P300000P0002ConveyorsMILESTONE 1120Technical Document IFC / IFF Issued08-Jan-1918-Jan-19 10P300000P0002ConveyorsMILESTONE 1130Purchase Order / Contract Issued17-Jan-1929-Jan-19 12P300000P0002ConveyorsMILESTONE 12000First Vendor Data Received06-Feb-1918-Feb-19 12P300000P0002ConveyorsMILESTONE 12010First Delivery or Mobilization Start07-May-1919-May-19 12P300000P0002ConveyorsMILESTONE 12020Last Delivery/Work Completed06-Jun-1918-Jun-19 12P300000P0004New PackageMILESTONE 110Package Initiation26-Apr-1826-Apr-1802-May-186P300000P0004New PackageMILESTONE 120Technical Specification Started03-May-1803-May-18 0P300000P0004New PackageMILESTONE 130Coordination Review Started10-May-1810-May-1803-May-18-7P300000P0004New PackageMILESTONE 140Technical Bid Doc and ATP Issued for Approval31-May-1831-May-18 0P300000P0004New PackageMILESTONE 150Bidders List Approved14-Jun-1814-Jun-18 0P300000P0004New PackageMILESTONE 160Advice to Procurement (ATP) Acknowledged18-Jun-1818-Jun-18 0

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 axis2) Planned, forecast and actual will be cumulated See attached photo as an example from the below table. Bidders List Approved - PLANBidders List Approved - FORECASTBidders List Approved - ACTUALBidders List Approved **bleep** PlannedBidders List Approved **bleep** ForecastBidders List Approved - **bleep** Actual Jan-18 111000Feb-18 234234Mar-18 116613910Apr-18 151210282120May-18 555332625Jun-18 589383434Jul-18 545433839Aug-18 987524646Sep-18 111314635960Oct-18 3746666 Nov-18 3606972 Dec-18 0006972 Jan-19 1107073 Feb-19 0007073 Mar-19 5107574 Apr-19 0107575 May-19 0007575 Jun-19 000 Jul-19 000 Aug-19 000 Sep-19 000 Oct-19 000 Nov-19 000 Dec-19 000 Jan-20 000 Feb-20 000 Mar-20 000

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

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 axis2) Planned, forecast and actual will be cumulated3) % 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.

luqmaanr

Frequent Visitor

7 years ago

Visualization in Power BI

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

Need Help

13 Replies

v-jiascu-msft

Microsoft Employee

7 years ago

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

Visualization in Power BI.pbix85 KB

luqmaanr

Frequent Visitor

7 years ago

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
Microsoft Employee
7 years ago
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
Frequent Visitor
7 years ago
The issue still not resolved. I have attached example in my previous posting.

Forum Discussion

Visualization in Power BI

13 Replies

Related content

Power Matrix visual

New Card Visual Missing After Latest Power BI Update

Power BI Custom Visuals

Power BI custom visual not sending Referer

Power BI dynamic visualizations