Forum Discussion
Creating a Shift Column Based of Date/Time
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
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.
2 Replies
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.
Hi, what if we have 3 shifts ?