cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
TimHellegers Regular Visitor
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 | 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.

 

3 REPLIES 3
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

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 344 members 3,401 guests
Please welcome our newest community members: