Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
swooters
Regular Visitor

Calculate sum when filtering on skills and date frames

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?

2 REPLIES 2
vanessafvg
Super User
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!




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

 

Screenshot visual.PNG

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

April Fabric Community Update

Fabric Community Update - April 2024

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

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

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