Date / Time Table Crossjoin issues (Shift Identification)

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