Creating a Shift Column Based of Date/Time

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.