cancel
Showing results for
Did you mean:
Regular Visitor

## Sum available contract hours filtered by date

Hi,

I need to calculate the SUM of contracthours per month based on the start and end date of the contract for employees.

`Employee nr | Start-date | End-date  | Hours------------|------------|-----------|-------1           | 1-1-2019   | 1-1-2999  | 402           | 1-1-2019   | 30-6-2019 | 403           | 1-1-2019   | 30-7-2019 | 403           | 1-8-2019   | 1-1-2999  | 32`

Based on the values above I would have 120 available hours in June,  80 in July and 72 in August.

How can I calculate this with a measure?

It will be used an a bar visual per month. But based on slicers.

3 REPLIES 3
Community Support Team

## Re: Sum available contract hours filtered by date

I’m confused about your sample data. How do you calculate 120 available hours in June, 80 in July and 72 in August? Which date are you based on? And what does the date of “1-1-2999” mean? Is it a correct date? Can you please share more details？

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regular Visitor

## Re: Sum available contract hours filtered by date

The start en end-date indicate the contract period of an employee. The year 2999 means that the contract has no end date yet and is active for an unknown period.

In the sample data you can see that one contract is valid until 30-06-2019. After this date the employee get a new contract for 32 hours. For the period of 1-1-2019 until 30-06-2019 the employee will work 40 hours a week and after that for 32 hours per week.

The calculation I did was based on the sample data, the formula that I need to create will have to look at months. So if I select June in the slicer it will calculate if a contract is active in June based on the start and end-date. In my sample data, employee 1, 2 and 3 have an active contract during this period and it will sum the contract hours (40+40+40). For August, employee 2 has no active contract anymore and employee 3 has a new one for 32 hours. (40 + 32).

Highlighted
Regular Visitor

## Re: Sum available contract hours filtered by date

Someone know how I can fix this in DAX?

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 82 members 2,700 guests
Recent signins: