Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.