cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
manoymon Regular Visitor
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:

 

EmployeePunch InPunch OutTotal HoursTotal Pay
A7:00:00 AM5:00:00 PM                    10.00             500.00
B8:00:00 AM4:30:00 PM                       8.50             600.00
C10:00:00 AM3:00:00 PM                       5.00             450.00

 

 

How do I go from that to below (done on Excel):

 

Time IntervalTotal 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:

 

Capture.JPG

 

 

Thank you very much.

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft Phil_Seamark
Microsoft

Re: Allocate Value Over a Period of Time

Hi @manoymon

 

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)

 

intervals.png

 

 

 

 

 

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

8 REPLIES 8
Microsoft Phil_Seamark
Microsoft

Re: Allocate Value Over a Period of Time

Hi @manoymon

 

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)

 

intervals.png

 

 

 

 

 

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

manoymon Regular Visitor
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.

manoymon Regular Visitor
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 Phil_Seamark
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.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

manoymon Regular Visitor
Regular Visitor

Re: Allocate Value Over a Period of Time

@Phil_Seamark

 

Here you go. 

Thank you in advance. 

 

 

StoreDateIN_TIMEOUT_TIMEEmployeeTotal Hours Total Wages & Special Pay 
Downtown3/6/20172:59:00 PM8:30:00 PMC              5.49                                               60.72
Downtown3/6/20179:09:00 PM12:00:00 AMC              3.58                                               31.57
Downtown3/7/20172:56:00 PM8:02:00 PME              4.54                                               56.10
Downtown3/7/20178:32:00 PM11:02:00 PME              5.60                                               27.50
Downtown3/6/20175:23:00 AM11:33:00 AMF              3.60                                               70.96
Downtown3/6/201712:02:00 PM2:30:00 PMF              3.56                                               28.52
Downtown3/7/20175:32:00 AM11:32:00 AMF              3.14                                               68.89
Downtown3/7/201712:00:00 PM3:00:00 PMF              3.19                                               34.50
Downtown3/6/20175:24:00 AM11:35:00 AMH              4.48                                               67.98
Downtown3/6/201712:05:00 PM3:07:00 PMH              3.94                                               33.33
Downtown3/7/20175:25:00 AM11:34:00 AMH              4.52                                               67.65
Downtown3/7/201712:04:00 PM3:08:00 PMH              3.96                                               33.77
Downtown3/6/20175:24:00 AM10:28:00 AML              7.27                                               63.38
Downtown3/6/201711:00:00 AM2:59:00 PML              5.16                                               49.88
Downtown3/7/20175:27:00 AM10:03:00 AML              0.01                                               57.63
Downtown3/7/201710:36:00 AM3:05:00 PML              3.08                                               56.25
Downtown3/6/20173:07:00 PM8:02:00 PMQ              5.38                                               54.12
Downtown3/6/20178:32:00 PM11:00:00 PMQ              2.82                                               27.17
Downtown3/7/20173:03:00 PM7:37:00 PMQ              5.18                                               50.38
Downtown3/7/20178:07:00 PM11:00:00 PMQ              2.92                                               31.79
Downtown3/7/20173:06:00 PM8:34:00 PMT              3.65                                               60.06
Downtown3/7/20179:04:00 PM11:00:00 PMT              3.48                                               21.34
Downtown3/6/20176:12:00 AM11:04:00 AMU              4.55                                               53.68
Downtown3/6/201711:30:00 AM2:42:00 PMU              4.08                                               35.31
Downtown3/7/20177:00:00 AM10:00:00 AMU              4.50                                               33.00
Downtown3/7/201710:30:00 AM2:45:00 PMU              4.03                                               46.75
Downtown3/6/20177:29:00 AM11:03:00 AMV              1.56                                               39.16
Downtown3/6/201711:33:00 AM3:03:00 PMV              3.19                                               38.61
Downtown3/7/20177:28:00 AM10:37:00 AMV              3.57                                               34.54
Downtown3/7/201711:05:00 AM3:01:00 PMV              3.25                                               43.34
Downtown3/7/201710:24:00 AM12:00:00 PMX              5.64                                               17.60
Downtown3/7/201712:28:00 PM4:53:00 PMX              6.69                                               48.62
Downtown3/6/20178:28:00 AM10:43:00 AMEEE              1.22                                               20.78
Downtown3/6/201711:13:00 AM2:19:00 PMEEE              4.08                                               28.37
Downtown3/7/20178:13:00 AM10:49:00 AMEEE              2.55                                               23.70
Downtown3/7/201711:16:00 AM2:26:00 PMEEE              3.08                                               29.01
Downtown3/6/20175:26:00 AM11:34:00 AMGGG              5.52                                               84.57
Downtown3/6/201712:04:00 PM3:11:00 PMGGG              3.73                                               43.04
Downtown3/7/20175:24:00 AM11:34:00 AMGGG              5.48                                               84.70
Downtown3/7/201712:04:00 PM3:08:00 PMGGG              3.56                                               42.08
Downtown3/6/20174:55:00 AM11:01:00 AMJJJ              4.60                                               67.10
Downtown3/6/201711:32:00 AM1:33:00 PMJJJ              3.44                                               22.22
Downtown3/7/20175:01:00 AM11:01:00 AMJJJ              3.06                                               66.11
Downtown3/7/201711:32:00 AM1:30:00 PMJJJ              4.33                                               21.67
Downtown3/6/20173:01:00 PM7:07:00 PMKKK              5.59                                               45.10
Downtown3/6/20177:38:00 PM10:56:00 PMKKK              2.04                                               36.30
Downtown3/7/20173:04:00 PM7:11:00 PMKKK              5.75                                               45.43
Downtown3/7/20177:41:00 PM11:00:00 PMKKK              1.80                                               36.52
Downtown3/6/20174:15:00 PM8:30:00 PMMMM              4.51                                               46.97
Downtown3/6/20179:05:00 PM11:05:00 PMMMM              3.51                                               21.89
Downtown3/7/20174:15:00 PM8:31:00 PMMMM              4.57                                               46.97
Downtown3/7/20179:00:00 PM11:30:00 PMMMM              3.47                                               27.50
Downtown3/7/20177:55:00 AM10:01:00 AMNNN              2.52                                               23.21
Downtown3/7/201710:30:00 AM3:02:00 PMNNN              3.98                                               49.83
Uptown3/6/20176:31:00 AM10:30:00 AMB              1.00                                               43.78
Uptown3/6/201711:00:00 AM2:56:00 PMB              8.11                                               43.34
Uptown3/7/20177:16:00 AM12:57:00 PMB              0.40                                               62.70
Uptown3/7/20171:29:00 PM3:02:00 PMB              8.54                                               17.16
Uptown3/6/201711:57:00 AM3:31:00 PMD              0.27                                               39.27
Uptown3/6/20173:57:00 PM9:05:00 PMD              5.74                                               56.65
Uptown3/7/201711:45:00 AM3:30:00 PMD              1.75                                               41.47
Uptown3/7/20174:01:00 PM9:04:00 PMD              6.56                                               55.55
Uptown3/6/20176:59:00 AM12:39:00 PMK              5.68                                               62.48
Uptown3/6/20171:02:00 PM1:03:00 PMK              0.01                                                 0.11
Uptown3/6/20176:27:00 AM12:30:00 PMO              4.16                                               66.55
Uptown3/6/201712:59:00 PM2:57:00 PMO              6.18                                               21.56
Uptown3/7/20176:59:00 AM2:38:00 PMO              1.85                                               84.15
Uptown3/6/20175:10:00 AM1:51:00 PMP              3.04                                               95.70
Uptown3/7/20175:03:00 AM1:35:00 PMP              4.55                                               93.94
Uptown3/6/20177:30:00 AM11:08:00 AMR              0.01                                               40.04
Uptown3/6/201711:31:00 AM3:01:00 PMR              8.39                                               38.61
Uptown3/7/20177:30:00 AM11:09:00 AMR              0.04                                               40.15
Uptown3/7/201711:33:00 AM3:01:00 PMR              5.98                                               38.28
Uptown3/6/20175:59:00 AM10:30:00 AMS              0.01                                               50.74
Uptown3/6/201711:00:00 AM2:31:00 PMS              8.11                                               39.49
Uptown3/7/20175:58:00 AM10:32:00 AMS              0.01                                               51.42
Uptown3/7/201711:02:00 AM2:30:00 PMS              8.52                                               39.04
Uptown3/6/20174:56:00 AM1:03:00 PMW              2.61                                               89.32
Uptown3/7/20174:54:00 AM1:31:00 PMW              2.29                                               94.82
Uptown3/7/20172:00:00 PM3:00:00 PMW              4.97                                               22.00
Uptown3/6/20177:29:00 AM10:47:00 AMY            10.05                                               36.30
Uptown3/6/201711:15:00 AM2:30:00 PMY              4.47                                               35.97
Uptown3/7/20178:03:00 AM10:45:00 AMY              1.48                                               29.81
Uptown3/7/201711:15:00 AM2:30:00 PMY              3.57                                               35.86
Uptown3/6/20176:02:00 AM4:23:00 PMZ              1.96                                            200.07
Uptown3/7/20176:25:00 AM4:06:00 PMZ              7.65                                            176.66
Microsoft Phil_Seamark
Microsoft

Re: Allocate Value Over a Period of Time

Hi again.

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

manoymon Regular Visitor
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.

ARibaudo Frequent Visitor
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

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

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!

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?

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

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!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors