Calculating required work hours by number of weeks selected in date slicer

[KMacauley]

Hi All,

I have been at this issue for the last three days and have not got anywhere.

The issue:
I have a two datasets of payroll data, one with hours worked on a daily basis and the second one the employee details and the number of hours expected to work in a given week.

This data is in PowerPivot, and I have created a Pivot Table that shows the sum of the Hrs Worked. The user has a slicer on Date so they can select the required date/s (anywhere from one day to n months).

The issue is I need to show in the Pivot Table the sum of Hrs Target and the difference between Sum of Hrs Worked and sum of Hrs Target.

Note: The calculation for sum of Hrs Target is NETWORKDAYS(<First Date Selected>, <Last Date Selected>)/5 * Weekly Hrs Target (of each employee)

Once I show these columns I then need to filter (possibly with a slicer) the records that have a difference of <0.

I've been able to calculate the NETWORKDAYS(<First Date Selected>, <Last Date Selected>)/5 from the slicer and store it in a named range, but I cannot work out how to pass that value back to the PowerPivot. If I knew that, I would create a calculation on each employee in the Hours Worked table.

Example Data:
Hours Worked
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Emp ID[/TD]
[TD]Date[/TD]
[TD]Hrs Worked[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]31/3/2014[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1/4/2014[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2/4/2014[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]3/4/2014[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]31/3/2014[/TD]
[TD]7.5[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1/4/2014[/TD]
[TD]7.5[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3/4/2014[/TD]
[TD]7.5[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]31/3/2014[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1/4/2014[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2/4/2014[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3/4/2014[/TD]
[TD]8[/TD]
[/TR]
</tbody>[/TABLE]

Employee Data
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Emp ID[/TD]
[TD]Weekly Hrs Target[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]37.5[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]37.5[/TD]
[/TR]
</tbody>[/TABLE]

Any help will be appreciated.

Thank you on advance.

 

[Tianbas]

create a calculated column in table Hours Worked -> Hrs Target =related(Employee Data[weekly Hrs worked]) / 5. Create another calculated column in the table or a measure in the pivot to calculated the delta between Hrs Target and Hours Worked and put a value filter on that

 

[scottsen]

To expand slightly on Tianbas's good suggestion... values in a slicer need to come from a calculated column. So if you want to filter on rows that have "difference <0" you will want to create a column that is say "MissedTarget" and set to True/False, "Yes/No", or whatever you like... and based on the calculated column Tianbas suggests. That way the value can end up in a slicer for filtering.

 

[KMacauley]

Hi Tianbas/Scottsen,

Thank you for you replies. I read your above comments, and then I realized I missed a glaring obvious omission to my above tables. An employee can have multiple entries for one day in the Hours Worked table due to working on multiple projects.

If I apply your above suggestions, it will over inflate Hrs Target. That is in the below example, employee 1 would sum up to 24 target hours on the 1/4

New Example Hours Worked table:
[TABLE="width: 702"]
<tbody>[TR]
[TD]Emp ID
[/TD]
[TD]Date
[/TD]
[TD]Hrs Worked
[/TD]
[TD]Project ID
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]31/3/2014
[/TD]
[TD]8
[/TD]
[TD]xyz
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]1/4/2014
[/TD]
[TD]3
[/TD]
[TD]xyz
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]1/4/2014
[/TD]
[TD]4
[/TD]
[TD]abc
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]1/4/2014
[/TD]
[TD]1
[/TD]
[TD]klm
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]2/4/2014
[/TD]
[TD]7
[/TD]
[TD]abc
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]3/4/2014
[/TD]
[TD]8
[/TD]
[TD]klm
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]31/3/2014
[/TD]
[TD]7.5
[/TD]
[TD]klm
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1/4/2014
[/TD]
[TD]5.5
[/TD]
[TD]klm
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1/4/2014
[/TD]
[TD]2
[/TD]
[TD]opq
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]3/4/2014
[/TD]
[TD]7.5
[/TD]
[TD]klm
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]31/3/2014
[/TD]
[TD]8
[/TD]
[TD]abc
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]1/4/2014
[/TD]
[TD]8
[/TD]
[TD]abc
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]2/4/2014
[/TD]
[TD]8
[/TD]
[TD]abc
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]3/4/2014
[/TD]
[TD]8
[/TD]
[TD]abc
[/TD]
[/TR]
</tbody>[/TABLE]

 

[scottsen]

I suspect an "hours worked per day" calculated column would still work out fine for you. Obviously it becomes a somewhat static value (like, it wouldn't pay attention to a slicer on Project), but I suspect you wouldn't care about that anyway when thinking about total hours per day per employee.

The calculated column would look something like:
HoursWorkedPerDay = calculate(sum(hoursworked[hrs worked]), all(hoursworked), hoursworked[empid] = earlier(hoursworked[empid]) && hoursworked[date]=earlier(hoursworked[date]))

There are ways to do with w/ calculated fields (measures), as well, but maybe a bit trickier to get the filtering to work (especially, if you want it on a slicer). But you can certainly imagine measures like [TotalHours] and [TargetHours] measures, and a [TargetDelta] measure to subtract those. Depending on what you need in the output, [TargetDelta] could return BLANK() if you want to filter out that value? Or apply a filter on the pivot table for negative values, instead?

 

[KMacauley]

Hi Scottsen,

I tried your formula, and if I add it as a calculated column on HoursWorked the error is;
"The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression."

If I add it onto the table EmployeeData, I get
"The value for column 'empid' in table 'HoursWorked' cannot be determined in the current context. Check that all columns referenced in the calculation expression exist, and that there are no circular dependencies. This can also occur when the formula for a measure refers directly to a column without performing any aggregation--such as sum, average, or count--on that column. The column does not have a single value; it has many values, one for each row of the table, and no row has been specified."


Am I missing something?

 

[scottsen]

"The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression."

That error message always means (basically) "you tried to do something too complex in your call to CALCULATE". I (incorrectly) tried to use the simple (and more efficient) version of the calculate parameter, but you aren't allowed to call EARLIER() in that case. So, you wrap it in a FILTER()...

=calculate(sum(hoursworked[hrs worked]), FILTER(ALL(hoursworked), hoursworked[emp id] = earlier(hoursworked[emp id]) && hoursworked[date]=earlier(hoursworked[date])))

 

[KMacauley]

Thanks Scott.

This worked successfully. I'll now work out the other formulas and see how I go.

Kim.

 

[KMacauley]

Hi Scott (or anyone else that can help),

I was wondering how I could do my difference flag?

I've tried the following;

Option 1:
a calculation column of [Missing Time]=IF(CALCULATE(SUM([Hours Worked Per Day])-SUM([Target Hours Per Day]), FILTER(ALL(HoursWorked), [Emp ID]=EARLIER([Emp ID]) ))<0, "Yes", "No")

The issue is when selecting a date or range of dates, it will show all records that had a flag of "Yes", even if the "Yes" is outside the date selection

Option 2:
a calculation column of [Missing Time]=IF(CALCULATE(SUM([Hours Worked Per Day])-SUM([Target Hours Per Day]), FILTER(ALL(HoursWorked), [Emp ID]=EARLIER([Emp ID]) && [Date]=EARLIER([Date]) ))<0, "Yes", "No")

The issue is when selecting a range of dates, it will show single days that have missing hours, but if the employee did extra time during the week and the aggregation equals the total Target Hours, then the employee should not be listed.

Option 3:
a calculation column of [Sum of Difference]=[Hours Worked Per Day]-[Target Hours Per Day]
a measure of msrMissingTime=IF([Sum of Difference]<0,"Yes","No")
a calculation column of [Missing Time]=[msrMissingTime]

The issue is when selecting a date or range of dates, it will show single days that have missing hours, but if the employee did extra time during the week and the aggregation equals the total Target Hours, then the employee should not be listed.


Basically, we need to be able to select a date range, say 31st Mar to the 6th Apr (not always a week, sometimes a day, sometimes a calendar month) and the pivot table will show the aggregated hours worked, the target hours, the difference, and the user can select "Yes/No" options on a Missing Time slicer. If "Yes" is selected, then show employees if the hours they have worked is less than the aggregated target hours.

Note: Target Hours is stored by employee and each day is 0.2 of a week. Weekends are always Saturday & Sunday but some employees might work 30 hrs M-F and make up the remaining hours on the weekend.

I hope this information is enough.

Kim.

 

[scottsen]

when selecting a date or range of dates, it will show single days that have missing hours

Can I just claim this is a requirements problem?

I mean, what does it mean to look at any specific date... wrt to a weekly goal?

I am fairly confused on your option 1 though... it's like you are saying the date slicer is not working? Are you using a separate calendar table?