Time Periods

Anything related to PowerPivot and DAX Formuale
IanW
Posts: 2
Joined: Wed Jul 15, 2020 2:21 pm

Time Periods

Postby IanW » Wed Jul 15, 2020 2:52 pm

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
Attachments
TimePeriod Example.xlsx
(11.78 KiB) Downloaded 39 times

ozeroth
Posts: 27
Joined: Mon Apr 06, 2015 6:02 am
Location: Auckland, New Zealand

Re: Time Periods

Postby ozeroth » Mon Jul 20, 2020 10:47 pm

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
Attachments
TimePeriod Example.xlsx
(280.39 KiB) Downloaded 34 times

IanW
Posts: 2
Joined: Wed Jul 15, 2020 2:21 pm

Re: Time Periods

Postby IanW » Wed Jul 22, 2020 2:58 pm

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
Attachments
TimePeriod Example2.xlsx
(30.93 KiB) Downloaded 32 times


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: Bing [Bot] and 5 guests