cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

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.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors