Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello everyone,
I would like your urgent help.
I would like to calculate the time of night work (21:00:00 - 05:00:00) between entry (date & time Start) and (date & time End) located.
example:
Date & time start | date & time end | Time between 21:00:00 and 05:00:00 |
10/02/2023 16:00:00 | 10/02/2023 23:00:00 | 02:00:00 |
11/02/2023 23:00:00 | 12/02/2023 08:00:00 | 06:00:00 |
15/02/2023 14:00:00 | 15/02/2023 23:30:00 | 02:30:00 |
thank you in advance for your help.
Solved! Go to Solution.
@Mediviz you can do something like this
Measure =
VAR __start =
MAX ( 'Table'[Date & time start] )
VAR __end =
MAX ( 'Table'[Date & time end] )
VAR hardStart =
DATEVALUE ( __start ) + TIME ( 21, 00, 00 )
VAR modStart =
IF ( hardStart > __start, hardStart, __start )
VAR hardEnd =
DATEVALUE ( __start ) + 1 // since night shift, the end date will always flow +1 from start
+ TIME ( 05, 00, 00 )
VAR modEnd =
IF ( hardEnd < __end, hardEnd, __end )
RETURN
modEnd - modStart
PFA
@Mediviz you can do something like this
Measure =
VAR __start =
MAX ( 'Table'[Date & time start] )
VAR __end =
MAX ( 'Table'[Date & time end] )
VAR hardStart =
DATEVALUE ( __start ) + TIME ( 21, 00, 00 )
VAR modStart =
IF ( hardStart > __start, hardStart, __start )
VAR hardEnd =
DATEVALUE ( __start ) + 1 // since night shift, the end date will always flow +1 from start
+ TIME ( 05, 00, 00 )
VAR modEnd =
IF ( hardEnd < __end, hardEnd, __end )
RETURN
modEnd - modStart
PFA
User | Count |
---|---|
102 | |
84 | |
77 | |
70 | |
67 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |