Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
My database is based of Job ID that can run during 1st, 2nd or overlap between two.
I am having difficulty getting accurate resuts for shifts due to overlap between shifts.
1st shift 5 AM to 4.30 PM
2nd Shift 5.00 PM to 3.30AM
OFF Shift 3.30 AM to 5.00 AM.
What I want :
I want a forth result for "overlapping shift".
The custom column DAX I used (of course with help of this forum) is:
HI @Anonymous,
I tied to simple your formula and add the conditions deal with the 'second shift' situation, you can test with the below formula if it helps:
shift =
VAR _start =
TIMEVALUE ( Table[ActualStartTimeLocal] )
VAR _end =
TIMEVALUE ( Table[ActualEndTimeLocal] )
RETURN
IF (
MIN ( _start, _end ) = BLANK (),
BLANK (),
IF (
_start > TIME ( 3, 30, 00 )
&& _end < TIME ( 05, 00, 00 ),
"OFF SHFIT",
IF (
_start >= TIME ( 05, 00, 00 )
&& _end <= TIME ( 16, 30, 00 ),
"1st",
IF ( _start >= TIME ( 17, 00, 00 ) && _end <= TIME ( 3, 30, 00 ), "2nd" )
)
)
)
Regards,
Xiaoxin Sheng
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |