Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 Column
Shift Table
Solved! Go to 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.
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 ?
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
58 | |
45 | |
42 |