Showing results for 
Search instead for 
Did you mean: 
TimHellegers Regular Visitor
Regular Visitor

Sum available contract hours filtered by date



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 | 40
2 | 1-1-2019 | 30-6-2019 | 40
3 | 1-1-2019 | 30-7-2019 | 40
3 | 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.


Community Support Team
Community Support Team

Re: Sum available contract hours filtered by date

Hi @TimHellegers ,


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.

TimHellegers Regular Visitor
Regular Visitor

Re: Sum available contract hours filtered by date

Hi @v-xuding-msft ,


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

TimHellegers Regular Visitor
Regular Visitor

Re: Sum available contract hours filtered by date

Someone know how I can fix this in DAX?

Helpful resources

Can You Solve These Challenge

Challenge: Can You Solve These?

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

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

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.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

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

Users Online
Currently online: 46 members 1,009 guests
Please welcome our newest community members: