Calculate working hours without and with end date

[AltusTellus]

Hi all,

I have some troubles calculating the capacity of working hours per employee in the future but also in the past. The case is:

One employee has a schedule from 01.01.2021 until 31.03.2021 with a average of 32 hours per week. The same employee has a successive schedule from 01-04-2021 with a average of 40 hours per week. The end date is still unknown, but could be be filled in the future.

My table is:

EmployeeID	EmploymentID	StartDate	EndDate	AverageHoursWeek
500	900	01.01.2021	31.01.2021	32
500	901	01.04.2021		40

And off course I have a dimdate table connected.

What I want to report is that p.e. at 04.01.2021 the employee works 6,4 hours (32 hours divided by 5 working days). The same for p.e. 02.04.2021 but then 8 hours. Example:

04.01.2021	6,4
05.01.2021	6,4
...
01.04.2021	8
02.04.2021	8
et cetera	8

So I always need to report the average working hours with a reference date. Please give me a direction to come to the right calculation. In advance many thnx!

[lbendlin]

First step is to define what you mean by week.  Are all employees in the same time zone? Same country? Saturday half days?

Then you use GENERATESERIES to compute the daily work time as you describe, and then you create the measure for the cumulative total.

[AltusTellus]

Hi @lbendlin , all employees ar working in the same timezone and same country. And yes, I would like to except the weekend days, but that is the finishing touch 🙂

GENERATESERIES is not allowed with blank values, in my case the end date is sometimes blank. To be clear: an employee works 40 hours every week. How do I get this in a matrix for p.e. the year 2021, depending on if the employee still has an active employment? Can you help me further on? Many thnx!

[lbendlin · Accepted solution]

Use COALESCE to substitute potential blank values with the maximum date you want to use, for example 2021-12-31.