Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have a dataset of Contract Employee's start and end date and time. The business hours to be considered as 06:00-14:00. I need to show the total business hours worked on a trend chart, showing daily, weekly or monthly view. It's ok just to consider the Hour, i.e. 07:45 to 14:00(Business hour finish time) can be counted as 7 hours. So, ideally for 01st March, it should show 27 hours. Breakdown as below:
8 hours for ID 01
8 hours fro ID 02
14-07 = 7 hours for ID 03
12-08 = 4 hours for ID 04
Employee ID | Start date & Time | End date & Time |
01 | 27/02/2023 05:00 | 05/03/2023 09:00 |
02 | 28/02/2023 06:05 | 02/03/2023 14:30 |
03 | 01/03/2023 07:45 | 02/03/2023 09:45 |
04 | 01/03/2023 08:30 | 01/03/2023 12:45 |
05 | 02/03/2023 14:10 | 06/03/2023 10:30 |
06 | 02/03/2023 10:30 | 05/03/2023 08:30 |
There is also another huddle, Saturday and Sundays are holidays and so, shoudl always be counted as 0 hours. And so, 04th and 05th March should show 0 hours on the chart.
Please could you help with creating a measure to achieve this.
Thanks in advance.
Solved! Go to Solution.
Hi @Souvik0812 ,
I suggest you to try code as below to create a measure.
DateDiff =
VAR _CURRENT =
MAX ( 'Calendar'[Date] )
VAR _STEP1 =
ADDCOLUMNS (
'Table',
"Start", DATEVALUE ( 'Table'[Start date & Time] ),
"End", DATEVALUE ( 'Table'[End date & Time] )
)
VAR _STEP2 =
ADDCOLUMNS (
_STEP1,
"DateDiff",
VAR _START =
IF (
AND (
[Start date & Time]
<= _CURRENT + TIME ( 6, 0, 0 ),
[End date & Time]
>= _CURRENT + TIME ( 6, 0, 0 )
),
_CURRENT + TIME ( 6, 0, 0 ),
IF ( [Start date & Time] < _CURRENT + TIME ( 14, 0, 0 ), [Start date & Time] )
)
VAR _END =
IF (
AND (
[End date & Time]
>= _CURRENT + TIME ( 6, 0, 0 ),
[End date & Time]
<= _CURRENT + TIME ( 14, 0, 0 )
),
[End date & Time],
IF (
AND (
[Start date & Time]
< _CURRENT + TIME ( 14, 0, 0 ),
[End date & Time]
>= _CURRENT + TIME ( 14, 0, 0 )
),
_CURRENT + TIME ( 14, 0, 0 )
)
)
RETURN
DATEDIFF ( _START, _END, HOUR )
)
RETURN
SUMX ( _STEP2, [DateDiff] )
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Souvik0812 ,
I suggest you to try code as below to create a measure.
DateDiff =
VAR _CURRENT =
MAX ( 'Calendar'[Date] )
VAR _STEP1 =
ADDCOLUMNS (
'Table',
"Start", DATEVALUE ( 'Table'[Start date & Time] ),
"End", DATEVALUE ( 'Table'[End date & Time] )
)
VAR _STEP2 =
ADDCOLUMNS (
_STEP1,
"DateDiff",
VAR _START =
IF (
AND (
[Start date & Time]
<= _CURRENT + TIME ( 6, 0, 0 ),
[End date & Time]
>= _CURRENT + TIME ( 6, 0, 0 )
),
_CURRENT + TIME ( 6, 0, 0 ),
IF ( [Start date & Time] < _CURRENT + TIME ( 14, 0, 0 ), [Start date & Time] )
)
VAR _END =
IF (
AND (
[End date & Time]
>= _CURRENT + TIME ( 6, 0, 0 ),
[End date & Time]
<= _CURRENT + TIME ( 14, 0, 0 )
),
[End date & Time],
IF (
AND (
[Start date & Time]
< _CURRENT + TIME ( 14, 0, 0 ),
[End date & Time]
>= _CURRENT + TIME ( 14, 0, 0 )
),
_CURRENT + TIME ( 14, 0, 0 )
)
)
RETURN
DATEDIFF ( _START, _END, HOUR )
)
RETURN
SUMX ( _STEP2, [DateDiff] )
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
79 | |
77 | |
70 | |
68 | |
54 |
User | Count |
---|---|
107 | |
99 | |
88 | |
79 | |
67 |