cancel
Showing results for
Did you mean:
Regular Visitor

## 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!

Regular Visitor

## 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.

Regular Visitor

## 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!

Regular Visitor

## 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!

Regular Visitor

## 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.

Frequent 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

#### Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!