Pivot table and graph question (is this even possible)

[dennisstinissen]

I haven't been arround for a while, but after some time off I'm getting back on track with Excel.

I'm walking in circles with this one and I think this should be possible in Excel, but I can't seem to resolve it. It has to do with a graph I want to make and I use a pivot table, but hey I think I am off track.

I have a worksheet with data in a table
Record;Start date;Planned Date; End Date
Record 1; 20210101; 20210301;
Record 2; 20210201; 20210501; 20210419
Record 3; 20210214; 20210228; 20210318
...

And the result I would like to have is a graph (and pivot)
On the horizontal axis we have the months (januari 2021 until januari 2022)
On the vertical axis we have a line with the amount of new records (start date in that month) a a given month
On the vertical axis we have another line with the amount of closed records (end date in that month)
On the vertical axis we have a bar with the amount of records still open
On the vertical axis we have another line with the percentage of closed records as planned (end date earlier or same as planned date)

Could someone point me in the right direction?

 

[BSALV]

testing.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Record	Start date	Planned Date	End Date	startdate	planned date2	end date		min	jan-21		date	Start date	Planned Date	End Date	open
2	Record 1	20210101	20210301		jan-21	mrt-21	--		max	mei-21		jan-21	1	#N/B	0	1
3	Record 2	20210201	20210501	20210419	feb-21	mei-21	apr-21		delta	4		feb-21	2	#N/B	0	3
4	Record 3	20210214	20210228	20210318	feb-21	feb-21	mrt-21					mrt-21	0	1	1	3
5												apr-21	0	0	1	2
6												mei-21	0	#N/B	0	1
7												jun-21	0	#N/B	0	1
8												jul-21	0	#N/B	0	1
9												aug-21	0	#N/B	0	1
10												sep-21	0	#N/B	0	1
11												okt-21	0	#N/B	0	1
12												nov-21	0	#N/B	0	1
13												dec-21	0	#N/B	0	1
14												jan-22	0	#N/B	0	1
15												feb-22	0	#N/B	0	1
16
Blad5

Cell Formulas
Range	Formula
E2:E4	E2	=IF(LEN([@[Start date]]),DATE(LEFT([@[Start date]],4),MID([@[Start date]],5,2),1),"--")
F2:F4	F2	=IF(LEN([@[Planned Date]]),DATE(LEFT([@[Planned Date]],4),MID([@[Planned Date]],5,2),1),"--")
G2:G4	G2	=IF(LEN([@[ End Date]]),DATE(LEFT([@[ End Date]],4),MID([@[ End Date]],5,2),1),"--")
L2	L2	=J1
M2:M15	M2	=COUNTIF(Tabel2[startdate],$L2)
N2:N15	N2	=IF(O2>0,SUMPRODUCT((Tabel2[planned date2]<=Tabel2[end date])*(Tabel2[end date]=L2))/O2,NA())
O2:O15	O2	=COUNTIF(Tabel2[end date],$L2)
P2:P15	P2	=SUMPRODUCT((Tabel2[startdate]<=L2)*(L2<=Tabel2[end date]))
L3:L15	L3	=EDATE(L2,1)
J1	J1	=MIN(Tabel2[[startdate]:[end date]])
J2	J2	=MAX(Tabel2[[startdate]:[end date]])
J3	J3	=ROUND((J2-J1)/30,0)