Time Periods

[IanW]

Hi

I am trying to use Power pivot to create a roster for our staff, and have to ability to see the gaps in there roster.

I am trying to get Staff on the rows and Time Period (15 minute blocks) in columns
I have a date table and that is OK, but I cannot work out how to bring the TimePeriod table (96 rows of 15 Minute Blocks) into the mix

I cannot get the measure correct to show the EventID matching the entire duration of the Event.
For Example: For Staff Lisa for Event 07:00 to 08:00 I would need her Event ID to Show against 07:00, 07:15, 07:30 and 07:45
The EventID's are large enough to a sum to be valid .
If possible I would like to show the Client against the time as that is more useful

IF I get this I could write a calculated column to see if these events overlap a specific time range of my unallocated event (True/False)
I am wanting to filter the pivot tables on the False entries to find the gaps in the roster.

Is anybody able to help?
is my logic sound or is there a better way?

Thanks

Ian

[ozeroth]

Hi Ian,

I have attached a sample of how I would set up the data model.

1. Schedule table is expanded so that for each original row there is one row per TimeID (15 minute block). I did this in Power Query.
2. The Schedule table is related to Date & Time dimension tables.
3. Create a measure showing the client names. I did this with CONCATENATEX to allow handling of multiple values. You could just use SELECTEDVALUE or VALUES if there will only ever be one client per staff member at a time.
3. Create a PivotTable in line with your sample tables (you may want to add some way of filtering or displaying Date)
4. Use conditional formatting as desired on the PivotTable.



Regards,
Owen

[IanW]

Hi Owen

Thanks for this.
Unfortunately I cannot expand the fact Schedule table as it is already large (15000 new rows per week)

I have added an updated another example with how I have been able to achieve the Result with a table and formula.

I am thinking I need the Time Period table as an unrelated table and maybe somehow use banding to achieve the result.
The table achieves the result by Data[TimeFrom] <= TimePeriod[StartTime] && Data[TimeTo] >= TimePeriod[EndTime]

I have tried to create a measure with the logic above but I cannot get this to work.


Thanks

Ian