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

 

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
Highlighted
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
Highlighted
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 Helper I
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.

manoymon Helper I
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 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 Helper I
Helper I

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 Helper I
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.

ARibaudo
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

Helpful resources

Announcements
New Ranks Launched March 24th!

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!

‘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

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.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors