Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Phil_Seamark
Employee
Employee

Hi @Anonymous

 

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

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

Phil_Seamark
Employee
Employee

Hi @Anonymous

 

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!

Anonymous
Not applicable

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

Hi @Anonymous,

 

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!

Anonymous
Not applicable

@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

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!

Anonymous
Not applicable

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.

Anonymous
Not applicable

Thank you very much for this.

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.