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.
Hello,
I am looking to use time intelligence to analyze timecard table that has clock in and clock out values. I'd not only like to see calculations by day, but by hour and quarter hour as well.
Example TimeCardData Table
Employee | ClockInTime | ClockOutTime |
A | 8:55:00 AM | 12:30:00 PM |
A | 1:00:00 PM | 4:50:00 PM |
B | 8:20:00 AM | 4:30:00 PM |
C | 7:30:00 AM | 11:20:00 AM |
C | 12:00:00 PM | 4:30:00 PM |
I'd like to connect this to a Time Dimension table that has the fields of Time (h:m), Hour (h), and Quarter Hour. Quarter hour can either be formatted as 1st, 2nd, 3rd, 4th or 7.1, 7.2, 7.3, 7.4,.... I'm open to other suggestions for this.
I need a measure that calculates the total minutes worked. For example, with the above data for 7 AM through 9:59 AM:
Hour | 7 AM (7-7:59 AM Hour) | 8 AM (8-8:59 AM Hour) | 9 AM (9-9:59 AM Hour) |
Minutes worked | 30 | 105 | 180 |
Where for reference, in the 7 o clock hour, Employee C worked 30 minutes, in the 8 o clock hour Employee C worked 60 minutes, employee B worked 40 minutes, and employee A worked 5 minutes, and so on.
I was told that DATEDIFF using MINUTES would be the way to go, but I have no idea how to build the relationship to the Time dimension table with having two times I am using in the TimeCardData fact table.
Any help would be greatly appreciated!
Solved! Go to Solution.
Thanks @v-stephen-msft. While your solution works, the end result is not as dynamic as I was looking for.
I ended up creating a time table in sql with quarter hour increments. I created a view in SSMS with logic to calculate hours worked based on clock in / clock out times per quarter hour increment. I was hoping a measure would be able to do this all for me, but this SQL view resolved my roadblocks.
Hi @rlussky ,
It is suggested to use the calculated columns.
Create the following columns
7 AM (7-7:59 AM Hour) =
VAR _in = [ClockInTime]
VAR _out = [ClockOutTime]
VAR _min =
TIME ( 7, 0, 0 )
VAR _max =
TIME ( 8, 0, 0 )
RETURN
IF (
_in < _min
&& _out > _min
&& _out < _max,
HOUR ( _out - _min ) * 60
+ MINUTE ( _out - _min ),
IF (
_in > _min
&& _out < _max,
HOUR ( _out - _in ) * 60
+ MINUTE ( _out - _in ),
IF (
_in > _min
&& _out > _max
&& _in < _max,
HOUR ( _max - _in ) * 60
+ MINUTE ( _max - _in ),
IF (
_in < _min
&& _out > _max,
HOUR ( _max - _min ) * 60
+ MINUTE ( _max - _min )
)
)
)
)
8 AM (8-8:59 AM Hour) =
VAR _in = [ClockInTime]
VAR _out = [ClockOutTime]
VAR _min =
TIME ( 8, 0, 0 )
VAR _max =
TIME ( 9, 0, 0 )
RETURN
IF (
_in < _min
&& _out > _min
&& _out < _max,
HOUR ( _out - _min ) * 60
+ MINUTE ( _out - _min ),
IF (
_in > _min
&& _out < _max,
HOUR ( _out - _in ) * 60
+ MINUTE ( _out - _in ),
IF (
_in > _min
&& _out > _max
&& _in < _max,
HOUR ( _max - _in ) * 60
+ MINUTE ( _max - _in ),
IF (
_in < _min
&& _out > _max,
HOUR ( _max - _min ) * 60
+ MINUTE ( _max - _min )
)
)
)
)
9 AM (9-9:59 AM Hour) =
VAR _in = [ClockInTime]
VAR _out = [ClockOutTime]
VAR _min =
TIME ( 9, 0, 0 )
VAR _max =
TIME ( 10, 0, 0 )
RETURN
IF (
_in < _min
&& _out > _min
&& _out < _max,
HOUR ( _out - _min ) * 60
+ MINUTE ( _out - _min ),
IF (
_in > _min
&& _out < _max,
HOUR ( _out - _in ) * 60
+ MINUTE ( _out - _in ),
IF (
_in > _min
&& _out > _max
&& _in < _max,
HOUR ( _max - _in ) * 60
+ MINUTE ( _max - _in ),
IF (
_in < _min
&& _out > _max,
HOUR ( _max - _min ) * 60
+ MINUTE ( _max - _min )
)
)
)
)
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks @v-stephen-msft. While your solution works, the end result is not as dynamic as I was looking for.
I ended up creating a time table in sql with quarter hour increments. I created a view in SSMS with logic to calculate hours worked based on clock in / clock out times per quarter hour increment. I was hoping a measure would be able to do this all for me, but this SQL view resolved my roadblocks.
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |