cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Regular Visitor

## Calculate sum when filtering on skills and date frames

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?

2 REPLIES 2
Super User

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

If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!

Regular Visitor

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'.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

#### Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors