Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I am trying to show graphically our labor cost in a 15-minute Time Interval, but the problem is, I only have the data below to work with:
Employee | Punch In | Punch Out | Total Hours | Total Pay |
A | 7:00:00 AM | 5:00:00 PM | 10.00 | 500.00 |
B | 8:00:00 AM | 4:30:00 PM | 8.50 | 600.00 |
C | 10:00:00 AM | 3:00:00 PM | 5.00 | 450.00 |
How do I go from that to below (done on Excel):
Time Interval | Total Pay (Employee A) | Total Pay (Employee B) | Total Pay (Employee C) | Total Pay |
7:00:00 AM | 12.50 | - | - | 12.50 |
7:15:00 AM | 12.50 | - | - | 12.50 |
7:30:00 AM | 12.50 | - | - | 12.50 |
7:45:00 AM | 12.50 | - | - | 12.50 |
8:00:00 AM | 12.50 | 17.65 | - | 30.15 |
8:15:00 AM | 12.50 | 17.65 | - | 30.15 |
8:30:00 AM | 12.50 | 17.65 | - | 30.15 |
8:45:00 AM | 12.50 | 17.65 | - | 30.15 |
9:00:00 AM | 12.50 | 17.65 | - | 30.15 |
9:15:00 AM | 12.50 | 17.65 | - | 30.15 |
9:30:00 AM | 12.50 | 17.65 | - | 30.15 |
9:45:00 AM | 12.50 | 17.65 | - | 30.15 |
10:00:00 AM | 12.50 | 17.65 | 22.50 | 52.65 |
10:15:00 AM | 12.50 | 17.65 | 22.50 | 52.65 |
10:30:00 AM | 12.50 | 17.65 | 22.50 | 52.65 |
10:45:00 AM | 12.50 | 17.65 | 22.50 | 52.65 |
11:00:00 AM | 12.50 | 17.65 | 22.50 | 52.65 |
11:15:00 AM | 12.50 | 17.65 | 22.50 | 52.65 |
11:30:00 AM | 12.50 | 17.65 | 22.50 | 52.65 |
11:45:00 AM | 12.50 | 17.65 | 22.50 | 52.65 |
12:00:00 PM | 12.50 | 17.65 | 22.50 | 52.65 |
12:15:00 PM | 12.50 | 17.65 | 22.50 | 52.65 |
12:30:00 PM | 12.50 | 17.65 | 22.50 | 52.65 |
12:45:00 PM | 12.50 | 17.65 | 22.50 | 52.65 |
1:00:00 PM | 12.50 | 17.65 | 22.50 | 52.65 |
1:15:00 PM | 12.50 | 17.65 | 22.50 | 52.65 |
1:30:00 PM | 12.50 | 17.65 | 22.50 | 52.65 |
1:45:00 PM | 12.50 | 17.65 | 22.50 | 52.65 |
2:00:00 PM | 12.50 | 17.65 | 22.50 | 52.65 |
2:15:00 PM | 12.50 | 17.65 | 22.50 | 52.65 |
2:30:00 PM | 12.50 | 17.65 | 22.50 | 52.65 |
2:45:00 PM | 12.50 | 17.65 | 22.50 | 52.65 |
3:00:00 PM | 12.50 | 17.65 | - | 30.15 |
3:15:00 PM | 12.50 | 17.65 | - | 30.15 |
3:30:00 PM | 12.50 | 17.65 | - | 30.15 |
3:45:00 PM | 12.50 | 17.65 | - | 30.15 |
4:00:00 PM | 12.50 | 17.65 | - | 30.15 |
4:15:00 PM | 12.50 | 17.65 | - | 30.15 |
4:30:00 PM | 12.50 | - | - | 12.50 |
4:45:00 PM | 12.50 | - | - | 12.50 |
And eventually leads to the graph below:
Thank you very much.
Solved! Go to Solution.
Hi @Anonymous
I managed to get this going with your test data.
Step 1) Create a Time interval table in the Query Editor by creating a blank query and adding the following code (click advanced Editor and paste this code in)
let Source = List.Generate( ()=>[i=0,j=0], each[i] < 24, each if [j] < 45 then [i=[i] , j=[j] + 15] else [i=[i]+1,j=0],each Number.ToText([i]) & ":" & Number.ToText([j])), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type time}}) in #"Changed Type"
This will generate a table of times in 15 minute intervals.
Set the name of this table to be Times
Step 2) Back in Power BI, Create a New Table and use the following code
Expanded Table = SELECTCOLUMNS( FILTER( CROSSJOIN(Data,Times), [Punch In]<='Times'[Column1] && 'Data'[Punch Out] >'Times'[Column1] ), "Employee",[Employee], "Time Inteval",[Column1], "Total Pay",[Total Pay] )
This combines your existing data (which I have named as a table called Data) with the table we created in Power Query at step 1
Now add a calculated column to the Expanded Table from step 2) using this code
Interval Pay = DIVIDE( 'Expanded Table'[Total Pay], CALCULATE( COUNTROWS('Expanded Table'), FILTER(ALL('Expanded Table'), 'Expanded Table'[Employee]=EARLIER('Expanded Table'[Employee]) ) ) )
Now you can create a visual like the one below (or a line chart)
Is it possible to sort by date and Company i.e. we have another column that has date and Company
Hi @Anonymous
I managed to get this going with your test data.
Step 1) Create a Time interval table in the Query Editor by creating a blank query and adding the following code (click advanced Editor and paste this code in)
let Source = List.Generate( ()=>[i=0,j=0], each[i] < 24, each if [j] < 45 then [i=[i] , j=[j] + 15] else [i=[i]+1,j=0],each Number.ToText([i]) & ":" & Number.ToText([j])), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type time}}) in #"Changed Type"
This will generate a table of times in 15 minute intervals.
Set the name of this table to be Times
Step 2) Back in Power BI, Create a New Table and use the following code
Expanded Table = SELECTCOLUMNS( FILTER( CROSSJOIN(Data,Times), [Punch In]<='Times'[Column1] && 'Data'[Punch Out] >'Times'[Column1] ), "Employee",[Employee], "Time Inteval",[Column1], "Total Pay",[Total Pay] )
This combines your existing data (which I have named as a table called Data) with the table we created in Power Query at step 1
Now add a calculated column to the Expanded Table from step 2) using this code
Interval Pay = DIVIDE( 'Expanded Table'[Total Pay], CALCULATE( COUNTROWS('Expanded Table'), FILTER(ALL('Expanded Table'), 'Expanded Table'[Employee]=EARLIER('Expanded Table'[Employee]) ) ) )
Now you can create a visual like the one below (or a line chart)
I ran into a bit of a wrinkle.
My mistake in not mentioning in the original post that the data covers multiple dates. I didn't think it would have an effect.
Thanks in advance for the time and patience.
Hi @Anonymous,
Can you show me what the data might look like for other days? I'll have a crack tonight.
Here you go.
Thank you in advance.
Store | Date | IN_TIME | OUT_TIME | Employee | Total Hours | Total Wages & Special Pay |
Downtown | 3/6/2017 | 2:59:00 PM | 8:30:00 PM | C | 5.49 | 60.72 |
Downtown | 3/6/2017 | 9:09:00 PM | 12:00:00 AM | C | 3.58 | 31.57 |
Downtown | 3/7/2017 | 2:56:00 PM | 8:02:00 PM | E | 4.54 | 56.10 |
Downtown | 3/7/2017 | 8:32:00 PM | 11:02:00 PM | E | 5.60 | 27.50 |
Downtown | 3/6/2017 | 5:23:00 AM | 11:33:00 AM | F | 3.60 | 70.96 |
Downtown | 3/6/2017 | 12:02:00 PM | 2:30:00 PM | F | 3.56 | 28.52 |
Downtown | 3/7/2017 | 5:32:00 AM | 11:32:00 AM | F | 3.14 | 68.89 |
Downtown | 3/7/2017 | 12:00:00 PM | 3:00:00 PM | F | 3.19 | 34.50 |
Downtown | 3/6/2017 | 5:24:00 AM | 11:35:00 AM | H | 4.48 | 67.98 |
Downtown | 3/6/2017 | 12:05:00 PM | 3:07:00 PM | H | 3.94 | 33.33 |
Downtown | 3/7/2017 | 5:25:00 AM | 11:34:00 AM | H | 4.52 | 67.65 |
Downtown | 3/7/2017 | 12:04:00 PM | 3:08:00 PM | H | 3.96 | 33.77 |
Downtown | 3/6/2017 | 5:24:00 AM | 10:28:00 AM | L | 7.27 | 63.38 |
Downtown | 3/6/2017 | 11:00:00 AM | 2:59:00 PM | L | 5.16 | 49.88 |
Downtown | 3/7/2017 | 5:27:00 AM | 10:03:00 AM | L | 0.01 | 57.63 |
Downtown | 3/7/2017 | 10:36:00 AM | 3:05:00 PM | L | 3.08 | 56.25 |
Downtown | 3/6/2017 | 3:07:00 PM | 8:02:00 PM | Q | 5.38 | 54.12 |
Downtown | 3/6/2017 | 8:32:00 PM | 11:00:00 PM | Q | 2.82 | 27.17 |
Downtown | 3/7/2017 | 3:03:00 PM | 7:37:00 PM | Q | 5.18 | 50.38 |
Downtown | 3/7/2017 | 8:07:00 PM | 11:00:00 PM | Q | 2.92 | 31.79 |
Downtown | 3/7/2017 | 3:06:00 PM | 8:34:00 PM | T | 3.65 | 60.06 |
Downtown | 3/7/2017 | 9:04:00 PM | 11:00:00 PM | T | 3.48 | 21.34 |
Downtown | 3/6/2017 | 6:12:00 AM | 11:04:00 AM | U | 4.55 | 53.68 |
Downtown | 3/6/2017 | 11:30:00 AM | 2:42:00 PM | U | 4.08 | 35.31 |
Downtown | 3/7/2017 | 7:00:00 AM | 10:00:00 AM | U | 4.50 | 33.00 |
Downtown | 3/7/2017 | 10:30:00 AM | 2:45:00 PM | U | 4.03 | 46.75 |
Downtown | 3/6/2017 | 7:29:00 AM | 11:03:00 AM | V | 1.56 | 39.16 |
Downtown | 3/6/2017 | 11:33:00 AM | 3:03:00 PM | V | 3.19 | 38.61 |
Downtown | 3/7/2017 | 7:28:00 AM | 10:37:00 AM | V | 3.57 | 34.54 |
Downtown | 3/7/2017 | 11:05:00 AM | 3:01:00 PM | V | 3.25 | 43.34 |
Downtown | 3/7/2017 | 10:24:00 AM | 12:00:00 PM | X | 5.64 | 17.60 |
Downtown | 3/7/2017 | 12:28:00 PM | 4:53:00 PM | X | 6.69 | 48.62 |
Downtown | 3/6/2017 | 8:28:00 AM | 10:43:00 AM | EEE | 1.22 | 20.78 |
Downtown | 3/6/2017 | 11:13:00 AM | 2:19:00 PM | EEE | 4.08 | 28.37 |
Downtown | 3/7/2017 | 8:13:00 AM | 10:49:00 AM | EEE | 2.55 | 23.70 |
Downtown | 3/7/2017 | 11:16:00 AM | 2:26:00 PM | EEE | 3.08 | 29.01 |
Downtown | 3/6/2017 | 5:26:00 AM | 11:34:00 AM | GGG | 5.52 | 84.57 |
Downtown | 3/6/2017 | 12:04:00 PM | 3:11:00 PM | GGG | 3.73 | 43.04 |
Downtown | 3/7/2017 | 5:24:00 AM | 11:34:00 AM | GGG | 5.48 | 84.70 |
Downtown | 3/7/2017 | 12:04:00 PM | 3:08:00 PM | GGG | 3.56 | 42.08 |
Downtown | 3/6/2017 | 4:55:00 AM | 11:01:00 AM | JJJ | 4.60 | 67.10 |
Downtown | 3/6/2017 | 11:32:00 AM | 1:33:00 PM | JJJ | 3.44 | 22.22 |
Downtown | 3/7/2017 | 5:01:00 AM | 11:01:00 AM | JJJ | 3.06 | 66.11 |
Downtown | 3/7/2017 | 11:32:00 AM | 1:30:00 PM | JJJ | 4.33 | 21.67 |
Downtown | 3/6/2017 | 3:01:00 PM | 7:07:00 PM | KKK | 5.59 | 45.10 |
Downtown | 3/6/2017 | 7:38:00 PM | 10:56:00 PM | KKK | 2.04 | 36.30 |
Downtown | 3/7/2017 | 3:04:00 PM | 7:11:00 PM | KKK | 5.75 | 45.43 |
Downtown | 3/7/2017 | 7:41:00 PM | 11:00:00 PM | KKK | 1.80 | 36.52 |
Downtown | 3/6/2017 | 4:15:00 PM | 8:30:00 PM | MMM | 4.51 | 46.97 |
Downtown | 3/6/2017 | 9:05:00 PM | 11:05:00 PM | MMM | 3.51 | 21.89 |
Downtown | 3/7/2017 | 4:15:00 PM | 8:31:00 PM | MMM | 4.57 | 46.97 |
Downtown | 3/7/2017 | 9:00:00 PM | 11:30:00 PM | MMM | 3.47 | 27.50 |
Downtown | 3/7/2017 | 7:55:00 AM | 10:01:00 AM | NNN | 2.52 | 23.21 |
Downtown | 3/7/2017 | 10:30:00 AM | 3:02:00 PM | NNN | 3.98 | 49.83 |
Uptown | 3/6/2017 | 6:31:00 AM | 10:30:00 AM | B | 1.00 | 43.78 |
Uptown | 3/6/2017 | 11:00:00 AM | 2:56:00 PM | B | 8.11 | 43.34 |
Uptown | 3/7/2017 | 7:16:00 AM | 12:57:00 PM | B | 0.40 | 62.70 |
Uptown | 3/7/2017 | 1:29:00 PM | 3:02:00 PM | B | 8.54 | 17.16 |
Uptown | 3/6/2017 | 11:57:00 AM | 3:31:00 PM | D | 0.27 | 39.27 |
Uptown | 3/6/2017 | 3:57:00 PM | 9:05:00 PM | D | 5.74 | 56.65 |
Uptown | 3/7/2017 | 11:45:00 AM | 3:30:00 PM | D | 1.75 | 41.47 |
Uptown | 3/7/2017 | 4:01:00 PM | 9:04:00 PM | D | 6.56 | 55.55 |
Uptown | 3/6/2017 | 6:59:00 AM | 12:39:00 PM | K | 5.68 | 62.48 |
Uptown | 3/6/2017 | 1:02:00 PM | 1:03:00 PM | K | 0.01 | 0.11 |
Uptown | 3/6/2017 | 6:27:00 AM | 12:30:00 PM | O | 4.16 | 66.55 |
Uptown | 3/6/2017 | 12:59:00 PM | 2:57:00 PM | O | 6.18 | 21.56 |
Uptown | 3/7/2017 | 6:59:00 AM | 2:38:00 PM | O | 1.85 | 84.15 |
Uptown | 3/6/2017 | 5:10:00 AM | 1:51:00 PM | P | 3.04 | 95.70 |
Uptown | 3/7/2017 | 5:03:00 AM | 1:35:00 PM | P | 4.55 | 93.94 |
Uptown | 3/6/2017 | 7:30:00 AM | 11:08:00 AM | R | 0.01 | 40.04 |
Uptown | 3/6/2017 | 11:31:00 AM | 3:01:00 PM | R | 8.39 | 38.61 |
Uptown | 3/7/2017 | 7:30:00 AM | 11:09:00 AM | R | 0.04 | 40.15 |
Uptown | 3/7/2017 | 11:33:00 AM | 3:01:00 PM | R | 5.98 | 38.28 |
Uptown | 3/6/2017 | 5:59:00 AM | 10:30:00 AM | S | 0.01 | 50.74 |
Uptown | 3/6/2017 | 11:00:00 AM | 2:31:00 PM | S | 8.11 | 39.49 |
Uptown | 3/7/2017 | 5:58:00 AM | 10:32:00 AM | S | 0.01 | 51.42 |
Uptown | 3/7/2017 | 11:02:00 AM | 2:30:00 PM | S | 8.52 | 39.04 |
Uptown | 3/6/2017 | 4:56:00 AM | 1:03:00 PM | W | 2.61 | 89.32 |
Uptown | 3/7/2017 | 4:54:00 AM | 1:31:00 PM | W | 2.29 | 94.82 |
Uptown | 3/7/2017 | 2:00:00 PM | 3:00:00 PM | W | 4.97 | 22.00 |
Uptown | 3/6/2017 | 7:29:00 AM | 10:47:00 AM | Y | 10.05 | 36.30 |
Uptown | 3/6/2017 | 11:15:00 AM | 2:30:00 PM | Y | 4.47 | 35.97 |
Uptown | 3/7/2017 | 8:03:00 AM | 10:45:00 AM | Y | 1.48 | 29.81 |
Uptown | 3/7/2017 | 11:15:00 AM | 2:30:00 PM | Y | 3.57 | 35.86 |
Uptown | 3/6/2017 | 6:02:00 AM | 4:23:00 PM | Z | 1.96 | 200.07 |
Uptown | 3/7/2017 | 6:25:00 AM | 4:06:00 PM | Z | 7.65 | 176.66 |
Yes it is to the minute.
No, shifts do not span days. Also worth noting, that 1 employee can have multiple shifts in 1 day.
Thank you very much for this.
As a beginner, I never would have figured that out myself.
User | Count |
---|---|
131 | |
108 | |
101 | |
67 | |
64 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |