Date / Time Table Crossjoin issues (Shift Identification)

[AlexanderPrime]

Hi there.

I'm having issues making a lookup to pull shift identifications against a Date/Time table in the 24 hour format (All UTC as this is pulling from D365)

I have a table with a Shift Time List like the below: (Calling it ShiftTimes table for this query)

Date	Group	Shift	Start	End
31/01/2022	A	Night	00:00	06:30
31/01/2022	A	Day	06:30	18:30
31/01/2022	A	Night	18:30	00:00
01/02/2022	A	Night	00:00	06:30
01/02/2022	A	Day	06:30	18:30
01/02/2022	A	Night	18:30	00:00

And I'm trying to get it so if a row in another table is reporting that a task is completed by Group A at a certain time, then it can flag if it's the Day or Night shift, however the shift times will vary (and is stored on D365) so the table records this by individual date like the above ShiftTimes table based on group.

Example of expected result

Date	Time	Group	Shift
31/01/2022	09:27	A	Day

I tried using Crossjoin with a Date/Time table. but to do one for each minute over a space of a year would have at least 525k multipled by the crossjoin result, and as such the Crossjoin table won't finish loading due to volume, am I overlooking something simple? 

[Mikelytics · Accepted solution]

Hi @AlexanderPrime 

I might have an approach with calculated columns. Typically I try to avoid calculated columns but here it might be a valid solution:

my base data:

ShiftTimes: I added in the table a shift ID using the Index column featuire from Power Query.

On top I added a Start and EndTime combining Date and Time. Here I did it as a calculated column. You can also do it in Power Query upfront.

Result of ShiftTimes Table

Activity Table: also with added combination of date and time

Based on the ActivityDateTime I identify in a calculated column the related shift ID with the following formula. Please be aware that there should not be an overlap of DateTime between different shifts. Otherwise you will need to use MAX or MIN instead of SELECTEDVALUE

RelatedShiftID = 

CALCULATE(
   SELECTEDVALUE(ShiftTimes[ShiftID]),
   FILTER(
      ShiftTimes,
      ShiftTimes[DateTimeStart] <= [ActivityDateTime] && 
      ShiftTimes[DateTimeEnd] >= [ActivityDateTime]
   )
)

now you have the ShiftID in your actvity table and you can based on the ID add the other information from the ShiftTimes table, like the Group

Group = 

var var_CurrentShiftID = [RelatedShiftID]

RETURN

CALCULATE(
   SELECTEDVALUE(ShiftTimes[Group]),
   ShiftTimes[ShiftID] = var_CurrentShiftID
)

and the Shift

Shift = 

var var_CurrentShiftID = [RelatedShiftID]

RETURN

CALCULATE(
   SELECTEDVALUE(ShiftTimes[Shift]),
   ShiftTimes[ShiftID] = var_CurrentShiftID
)

Best regards

Michael

-----------------------------------------------------

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

-----------------------------------------------------

LinkedIn

[AlexanderPrime]

Perfect. That did the trick, many thanks!

[djurecicK2]

Hi @AlexanderPrime ,

 Here is a possible idea- is everything between 00:00 and 06:30 and 18:30 to 00:00 always night shift? (and 06:30 to 18:30 always day shift?)

If you have the time of task completion, could you just write a statement that assigns shift based on these values?

If taskcompletiontime >= 06:30 && <=18:30, then day shift, else night shift 

[AlexanderPrime]

The shift times are subject to change so unfortunately this approach wouldn't work, I wish it was that straightforward though! Thanks.