Sum of working hours between two "sliced" dates

[adva]

Hi again

Since my last question yielded great help, I'll try again with another one:

I have a table containing employee numbers and their respective number of contractual working hours. Furthermore, the table contains a start date and end date showing when the given number of contractual working hours were valid between for the employee.

The table can therefore contain 1 or more rows for each employee, as their contractual number of working hours can increase/decrease.

I would like to do a sum of the contractual working hours that takes into account that an employee's working hours increase/decrease.

I was thinking something in the line of this:

=calculate(SUM(data_table[WORKINGHOURS]);data_table[start_date]<time_table[date]>data_table[end_date].

I would then slice/filter on time_table[date], so when doing this for ie. January 2011 this would give me x contractual working hours, while doing it for January 2012 I would get y hours if an alteration had happened for an employee.

This function hasn't worked, and I have also tried a similar, but using a datesbetween option as a filter. The latter option seems to give me difficulties with the start- and end dates being columns.

Hopefully one of you can help me out.

Thanks.

/Adam

 

[AlbertoFerrari]

Adam, the issue, as I told you, is what to show. Provide us an example of the numbers you want to see, and the formula will be easy.
The conditions I put are for the dates to be in the selected range. Thus, In February you would see only rows that have start after 1st of February and end before 28th of February.
By changing those conditions, you can obtain any kind of value but finding the correct condition depend on your specific business rules.

As a side note, I am seeing that Excel users (like you, I guess) tend to think that BI professionals already know their business rules. Believe me, this is not true. I have no idea what your numbers mean and what the start and end date represent. I am glad to help, but you need to understand that I don't know what your business rules are. I tend to ask to all of my customers: "tell me what your job is, speaking as if I am a 10 years old baby". Only after this explanation I start grabbing the meaning of tables and columns, and I can start doing my job.

Alberto
SQLBI