Calculate sum when filtering on skills and date frames

[swooters]

Could anyone please help me with my challenge below.

I have a table called "Headcount" and consists of the following columns:

- Employee

- EN (boolean that indicates whether this employee speaks English)

- NL (boolean that indicates whether this employee speaks Dutch)

- FR (boolean that indicates whether this employee speaks French)

- Start date contract

- End date contract

- Contract hours

With this table I now want to calculate the total sum of contract hours on each day for the next year, rolling. For this sum the contract hours of a certain employee is only taken into account when the date is in between the start and date of the contract of that specific employee.

Furthermore, I want to have a slicer on Skill in my report. Skill is a set of data containing EN, NL and FR. When I select 1 or more languages here the sum must only take into account those contract hours for those employees with those skills, without double counting employees that possess multiple skills.

Who could help me with this? I think there must come a nested measure?

[vanessafvg]

@swooters  are you able to provide some data?  or create some dummy data

[swooters]

Of course. See the table below. Please note that the ID - skill combination is unique in this table. (I already unpivoted the skill columns).

ID	Start	End	Min contract	Max contract	Skill
1	dinsdag 1 januari 2019	donderdag 31 december 2099	32	32	ES
1	dinsdag 1 januari 2019	donderdag 31 december 2099	32	32	EN
1	dinsdag 1 januari 2019	donderdag 31 december 2099	32	32	NL
5	dinsdag 1 januari 2019	donderdag 31 december 2099	32	32	ES
5	dinsdag 1 januari 2019	donderdag 31 december 2099	32	32	EN
5	dinsdag 1 januari 2019	donderdag 31 december 2099	32	32	NL
6	dinsdag 1 januari 2019	donderdag 31 december 2099	32	32	ES
6	dinsdag 1 januari 2019	donderdag 31 december 2099	32	32	EN
6	dinsdag 1 januari 2019	donderdag 31 december 2099	32	32	NL
7	dinsdag 1 januari 2019	donderdag 31 december 2099	32	32	ES
7	dinsdag 1 januari 2019	donderdag 31 december 2099	32	32	EN
7	dinsdag 1 januari 2019	donderdag 31 december 2099	32	32	NL
10	dinsdag 1 januari 2019	donderdag 31 december 2099	24	32	FR
10	dinsdag 1 januari 2019	donderdag 31 december 2099	24	32	EN
10	dinsdag 1 januari 2019	donderdag 31 december 2099	24	32	NL
11	dinsdag 1 januari 2019	donderdag 31 december 2099	32	32	ES
11	dinsdag 1 januari 2019	donderdag 31 december 2099	32	32	EN
11	dinsdag 1 januari 2019	donderdag 31 december 2099	32	32	NL
13	dinsdag 1 januari 2019	donderdag 31 december 2099	24	32	FR
13	dinsdag 1 januari 2019	donderdag 31 december 2099	24	32	EN
13	dinsdag 1 januari 2019	donderdag 31 december 2099	24	32	NL
14	dinsdag 1 januari 2019	donderdag 31 december 2099	32	32	ES
14	dinsdag 1 januari 2019	donderdag 31 december 2099	32	32	EN
14	dinsdag 1 januari 2019	donderdag 31 december 2099	32	32	NL
15	dinsdag 1 januari 2019	donderdag 31 december 2099	24	32	ES
15	dinsdag 1 januari 2019	donderdag 31 december 2099	24	32	EN
15	dinsdag 1 januari 2019	donderdag 31 december 2099	24	32	NL
16	dinsdag 1 januari 2019	donderdag 31 december 2099	20	32	ES
16	dinsdag 1 januari 2019	donderdag 31 december 2099	20	32	EN
16	dinsdag 1 januari 2019	donderdag 31 december 2099	20	32	NL
18	dinsdag 1 januari 2019	donderdag 31 december 2099	24	32	ES
18	dinsdag 1 januari 2019	donderdag 31 december 2099	24	32	EN
18	dinsdag 1 januari 2019	donderdag 31 december 2099	24	32	NL

I already have the following visual and slicers where I want to combine the blue lines with the minimal and maximal available contract hours, based on the moment in time (related to the start and end dates of the contracts of the different employees above) and the skills selected in the slicer 'Taal'.