List of active employees on a given date / in a date range & cumulative headcount

Please find the attached file, sheet "Solution" which is what I want to accomplish:

Part I:
Each employee is registered in sheet "EmployeeList".
Each transaction ("Joining", "promoted", "Leaving") is registered on a single line with a date in sheet "DataEntry".

I now want to create a table based on the data in these two sheets that shows a list of all employees that were active on a given date (E.g. Joined or promoted, not Left) or in a given date range.
This is where the first problem comes in: Since I only have a "Start" date for each position, I need to find the ending date for that position as well. If the employee started in Position 1 on 01.01.2016 and was promoted to Position 2 on 01.01.2017, he/she would then be active in Position 1 from 01.01.2016 until 31.12.2016.

Part II:
I want to be able to create charts/a dashboard showing the headcount per Position, per Team and per Gender.
I have tried different approaches (Pivot Tables, with a column for cumulative sums, but the problem is that when I filter on a date range using a slicer, the values always start at 0 instead of the actual value prior to the starting date in the filter).
I have also tried with Power Query, but unfortunately without success... I am able to create the cumulative headcount, but when I create a chart, it only plots those dates that have a transaction. I want to show each period in the chart. For example, if there are 2 employees being hired in January, 1 in April, and 1 employee leaves in June, the headcount would be 2 from January to March, 3 from April to May and 2 from June and onwards.
My chart only plots 2, 3, and 2 in January, April and June, and does not show the values for February, March and May...

How can I solve this?

Thank you in advance!

Part I

A9
=INDEX(EmployeeList!A:A,AGGREGATE(15,6,ROW(EmpTable)/(EmpTable[Joining Date]<=$B$6)/((EmpTable[Leaving Date]>=$B$6)+(EmpTable[Leaving Date]="")),ROWS(A$9:A9)))
B9
=LOOKUP(2,1/(INDEX(Table1,,2)=$A9)/(INDEX(Table1,,1)<=$B$6)/(INDEX(Table1,,3)<>"Leaving"),Table1[To Team])

E9
=LOOKUP(2,1/(INDEX(Table1,,2)=$A9)/(INDEX(Table1,,1)<=$B$6)/(INDEX(Table1,,3)<>"Leaving"),Table1[Date])

F9
=LOOKUP(2,1/(INDEX(Table1,,2)=$A9)/(Table1[From Position]=C9),Table1[Date])

Part II
Not sure if this useful, I don't know how to make chart.

Data & Employees sheet
P2 drag down
=IF([@Status]="Terminated",[@Date],IFERROR(LOOKUP(1,1/([@Employee]=[Employee])/([@[To Position]]=[From Position]),[Date]),TODAY()))

Q2 drag across and down
=IF(COLUMNS($Q2:Q2)>DATEDIF($D2,$P2,"m")+1,"",EOMONTH($D2,COLUMNS($Q2:Q2)))

Then use Power query to unpivot column Month 1 to Month 15
Result in Unpivot sheet

But I don't know how to make a chart from that data