Creating a Shift Column Based of Date/Time

[Anonymous]

Hi,

I am trying to create a new column that contains 1st and 2nd shift based off of data from my "completed" date/time column.  1st shift would be 4:30 AM - 4:29 PM and 2nd would be 4:30 PM - 4:29 AM.  I created a shift table as well, but when I create a relationship with completed, the date becomes 1899.  The idea is to create a shift slicer/toggle for management to use and determine qty completed per shift.  Any help would be greatly appreciated.

Completion ColumnShift Table  

[v-xicai · Accepted solution]

Hi @Anonymous ,

You can create a calculated table first of all, then create other columns in the new table like DAX below. Then you can create relationship between this new table and you fact data table on date field.

Table = SELECTCOLUMNS( CROSSJOIN( CALENDAR(MIN([COMPLETED]), MAX([COMPLETED])), GENERATESERIES( 0, TIME(23,0,0), TIME(0,30,0) ) ), "dateTime", [Date]& " " &[Value] )
 
Columns:
hour = HOUR([dateTime])
 
minute = MINUTE([dateTime])
 
key = [hour]&":"&[minute]
 
shift = var d=TIME(HOUR([dateTime]),MINUTE([dateTime]),SECOND([dateTime]))
return
IF(d>=TIME(4,30,0)&&d<=TIME(16,30,0),"1st","2st")

Result:

Best Regards,

Amy

Community Support Team _ Amy

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

[Fqnzr]

Hi, what if we have 3 shifts ?