Looking for Examples of Employee Schedule Calendar

[crichter14]

I'm trying to develop a pivot table that shows Employees as a dimension and their hours of work in the Measure.  I've tried all the key words I can think of, but I can't seem to find anything close.   (timesheet, calendar, payroll schedule, time card, etc).  

Suggestions?

My example is below:

Issues I'm running into specifically.  

1.  I'm getting dashes '-' in the pivot table that I can't seem to get rid of.  I'd like to replace them with the word 'OFF'

2.  I don't know how to get the totals tacked on to the end.

3. The header has a lot of additional information that I can't seem to tack on.  (i.e. hours of operations, pay period)

4.  User is going to want to see overtime also I'm going to ask him what that means to his analysis (per day, per week) 

5.  User will probably ask for vacation hours (again going to ask him how to identify those)

I'm just starting out on this build so I'm looking for ideas - not necessarily solutioning.  I checked patricn first to see what I could pull from his examples.  But no luck.

Thank you for reading.

CURRENT WEEK:

Note: Total hours take into account a 1 hour break Tues-Thursday and a 30 minute  break on Fridays	Monday 06/22	Tuesday 06/23	Wednesday 06/24	Thursday 06/25	Friday 06/26	Saturday 06/27	Sunday 06/28	Total
Hours of Operation	CLOSED	11AM-7PM	11AM-7PM	11A-7PM	10AM-6PM	9AM-5PM	10AM-4PM
Pay Period	Pay Period Ending A	Pay Period Ending A	Pay Period Ending B	Pay Period Ending B	Pay Period Ending B	Pay Period Ending B	Pay Period Ending B
Department Name
Emp1	OFF	OFF	10:30AM-7:30PM (9 HRS)	9AM-5PM (8 HRS)	9:30AM-6:30PM (9 HRS)	8:30AM-5:30PM (9 HRS)	9AM-4:30PM (7.5 HRS)	40 HRS
Emp2	OFF	10:30A-7:30PM (9 HRS)	9AM-5PM (8 HRS)	10:30AM-7:30PM (9 HRS)	9:30AM-5:30PM ( 8 HRS)	8:30AM-5:30PM (9 HRS)	OFF	40.5 HRS
Emp3	OFF	10:30AM-7:30PM (9 HRS)	10:30AM-7:30PM (9 HRS)	OFF	9:30AM-6:30PM (9 HRS)	8:30AM-5:30PM (9 HRS)	9AM-4:30PM (7.5 HRS)	40 HRS
Emp4	OFF	OFF	11AM-7:30PM (8.5 HRS)	10:30AM-6:30PM (8.5 HRS)	9:30AM-6:30PM (9 HRS)	8:30AM-5:30PM (9 HRS)	9:30AM-4:30PM (7 HRS)	40 HRS
Emp5	OFF	OFF	OFF	OFF	9:30AM-6:30PM (9 HRS)	8:30AM-5:30PM (9 HRS)	9:30AM-4:30PM (7 HRS)	25 HRS
Emp6	OFF	10:30AM-7:30PM (9 HRS)	10:30AM-6:30PM (8 HRS)	11AM-7:30PM (8.5 HRS)	VACATION (8 HRS)	VACATION (8 HRS)	OFF	40 HRS
Emp7	OFF	OFF	OFF	OFF	9:30AM-6:30PM (9 HRS)	8:30AM-5:30PM (9 HRS)	OFF	18 HRS

[Stoyan_Terziev]

Hey Crichter14,

That's the model approach you need to take. If you want to display 0'es for days off without having the records in fact, just Outer Join the Calendar Date (fully populated) to the Employee Master Employee Name (or ID) and then to the Fact Data to artificially bring those expected records then define this as:

LOAD
....
If(IsNull([Absolute Hours]), 0 , [Absolute Hours]) as [Absolute Hours]
Resident [Fact Data]
;