cancel
Showing results for
Did you mean:
Helper I

Allocate Value Over a Period of Time

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft

Re: Allocate Value Over a Period of Time

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)

Proud to be a Datanaut!

8 REPLIES 8
Microsoft

Re: Allocate Value Over a Period of Time

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)

Proud to be a Datanaut!

Helper I

Re: Allocate Value Over a Period of Time

Thank you very much for this.

As a beginner, I never would have figured that out myself.

Helper I

Re: Allocate Value Over a Period of Time

@Phil_Seamark

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.

Microsoft

Re: Allocate Value Over a Period of Time

Hi @manoymon,

Can you show me what the data might look like for other days?  I'll have a crack tonight.

Proud to be a Datanaut!

Highlighted
Helper I

Re: Allocate Value Over a Period of Time

@Phil_Seamark

Here you go.

 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
Microsoft

Re: Allocate Value Over a Period of Time

Hi again.

Is the finest grain down to the minute? And do shifts span days?

Proud to be a Datanaut!

Helper I

Re: Allocate Value Over a Period of Time

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.

Regular Visitor

Re: Allocate Value Over a Period of Time

Is it possible to sort by date and Company i.e. we have another column that has date and Company

Announcements

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors