Sum available contract hours filtered by date

[Anonymous]

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.

[v-xuding-msft]

Hi @Anonymous ,

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.

[Anonymous]

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

[Anonymous]

Someone know how I can fix this in DAX?