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]

I would love to check it, but I will need a workbook as a starting point, maybe with a few notes about the numbers you would like to see in the PivotTable.
This greatly helps reducing the time I put in finding the code, really.

Alberto
PowerPivot Workshop - The first PowerPivot course, produced by SQLBI - Tweet #ppws

 

[adva]

Hi again

Let me see if I can give you a more thorough explanation.

I have the following table (Group_member_details) with employee numbers, number of hours per month, and the period in which these hours are valid (start_data and end_date). I left the time stamp of entry there - I don't if it's useful.

Employee	Hours_month	start_date	end_date	TimeStamp
1	160,33	01-01-2012	01-01-2099	15-11-2012 16:10
2	160,33	01-01-2012	21-06-2012	16-11-2012 09:56
2	0	22-06-2012	01-01-2099	16-11-2012 10:27
3	138,67	01-01-2012	01-01-2099	16-11-2012 09:57
4	160,33	01-01-2012	01-01-2099	13-11-2012 15:11
5	134,33	06-02-2012	01-01-2099	13-11-2012 15:14
6	160,33	01-01-2012	03-02-2012	13-11-2012 16:41
6	160,33	06-02-2012	01-01-2099	16-11-2012 10:00

<colgroup><col><col><col span="2"><col></colgroup><tbody>
</tbody>

I would like to sum hours_month for various periods. These periods (mostly monthly are defined in my master_date_table, that of course has a unique entry of each date from 2012-01-01 up till the year 2040. (The date_table also include separate columns for month, year, etc.).

So as an example, I want to sum, from 2012-01-01:

160,33+160,33+138,67+160,33+160,33 (5 employees working this number of hours in this period.

In this should then be a dynamic calculation that functions with slicers applied from my master_date_table.

I sincerely hope you can help And that the above explanation of my issue is satisfying.

Thanks

/Adam

 

[AlbertoFerrari]

Adam, thanks for the explanation, it helps but, really, I need an Excel workbook.
The reason is pretty simple: in order to write a formula I need to enter data in Excel, create a data model, create meaningful examples and finally write the code and debug it.
Believe it or not, the preparation of the excel workbook takes 80/90% of the total time required to give a solution, and it can be brought to zero seconds just by sharing the workbook, maybe with less data than the original one, but with the structure already prepared. And I guess you already have it at hand.
This is the reason for I always ask for an Excel workbook as the starting point, otherwise, to study a problem, I need to find some time to prepare the environment. Since I find this preparation part pretty boring... I tend not to answer to questions that require me to follow the preparation step, unless the problem is really nice to study.

Alberto

 

[adva]

Sorry for my ignorance - and I really do feel like the new kid in class here - but how can I get the workbook sent/attached to you?

 

[AlbertoFerrari]

Don't worry, I wouldn't know the answer, either.
send me an email to alberto.ferrari@sqlbi.com, looks like the easiest path.

 

[AlbertoFerrari]

The formula is easy to write, the hard part is undertanding exactly what to compute

I tried this measure:

WorkingHours:=

CALCULATE (
    SUM ( Employee_Info[Hours_Month] );
    FILTER (
        Employee_Info;
        Employee_info[start_Date] >= MIN (Master_date_table[Date]) &&
        Employee_info[End_Date] <= MAX (Master_date_table[Date])
    )
)

The problem of this kind of formulas, is what to show at the aggregate level. For example, what do you want to show at the year level, if there are two rows, for the same employee, one for January and one for February? You can sum them, get the lat, show nothing... many options are available. You can choose them all by modifying the conditions inside FILTER. The pattern is always the same, finding the correct condition is only a matter of finding your specific business need.

Alberto
PowerPivot Workshop - The first PowerPivot course, produced by SQLBI - Tweet #ppws

 

[Laurent C]

Dudes, ever heard of cloud computing?
Filesharing over the internet can be done with Skydrive, Google docs, Dropbox, ...

 

[miguel.escobar]

Dudes, ever heard of cloud computing?
Filesharing over the internet can be done with Skydrive, Google docs, Dropbox, ...

I feel left out too!
Try creating a dropbox account, it's probably the easiest one.

 

[adva]

The formula is easy to write, the hard part is undertanding exactly what to compute

I tried this measure:

WorkingHours:=

CALCULATE (
    SUM ( Employee_Info[Hours_Month] );
    FILTER (
        Employee_Info;
        Employee_info[start_Date] >= MIN (Master_date_table[Date]) &&
        Employee_info[End_Date] <= MAX (Master_date_table[Date])
    )
)

The problem of this kind of formulas, is what to show at the aggregate level. For example, what do you want to show at the year level, if there are two rows, for the same employee, one for January and one for February? You can sum them, get the lat, show nothing... many options are available. You can choose them all by modifying the conditions inside FILTER. The pattern is always the same, finding the correct condition is only a matter of finding your specific business need.

Alberto
PowerPivot Workshop - The first PowerPivot course, produced by SQLBI - Tweet #ppws

Hi again

Thanks so much for your efforts Alberto (and your sly comments everyone else!)

I finally had time to test your code given.

The result I end up getting however doesn't return the wanted sum. It only returns a sum of approx. 641 hours (without any filter applied), which is the result of 4 employees of 160,33 hours (being the norm for many employees). Looking at which 4 employees are returned, it is only the ones with an end_date before infinity (infinity = 01-01-2099).

Furthermore, the sum can't be filtered by dates - it only gives a sum of 641 hours for January 2012, where all other months are blank - this even though the end date lies after January 2012, and therefore should be counted in ie. February.

I found the following example where you (Alberto) gives an example of counting rows between two dates (Alberto Ferrari : PowerPivot: Counting active days). I have tried to incorporate this in the formula you suggested, since I'm thinking this could be helpful, but still without luck unfortunately.

I really hope my explanation here can make it possible for someone (Alberto or others) to tweak and find a perfect solution

Thanks

/Adam