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？
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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).