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

Create an Uptime Column

Hi 

 

I have the following data set. 

 

I am trying to plot uptime for each calendar month.

 

ie the total of minutes in a calendar month - the sum of the down time in a given calendar month.

 

Thanks

 

Chris

 

CreatedDowntime (mins)

31/10/2017 09:05:53600
31/10/2017 10:05:5530
31/10/2017 15:10:330
31/10/2017 20:00:0930
01/11/2017 06:35:0145
01/11/2017 06:47:450
01/11/2017 15:11:3015
01/11/2017 17:41:262
01/11/2017 18:52:1715
01/11/2017 21:28:1715
02/11/2017 07:24:0410
02/11/2017 11:52:5110
03/11/2017 19:19:14null
03/11/2017 20:58:5325
03/11/2017 21:03:0210
03/11/2017 21:05:4710
04/11/2017 05:20:0115
04/11/2017 05:28:2415
04/11/2017 05:45:3560
04/11/2017 05:53:4920
04/11/2017 06:13:48180
04/11/2017 06:25:2040
04/11/2017 09:23:450
04/11/2017 12:12:04210
04/11/2017 12:47:0930
04/11/2017 14:30:43131
05/11/2017 01:03:150
05/11/2017 01:13:470
05/11/2017 17:00:5690
06/11/2017 08:03:1619
06/11/2017 08:08:2035
06/11/2017 08:31:258
06/11/2017 12:52:190
06/11/2017 14:22:4710
06/11/2017 21:59:360
07/11/2017 22:12:5030
08/11/2017 01:01:16180
08/11/2017 07:19:02null
08/11/2017 09:51:1630
08/11/2017 12:09:2610
08/11/2017 14:59:5510
08/11/2017 15:23:1190
09/11/2017 08:01:2460
09/11/2017 09:49:5630
09/11/2017 09:55:5930
09/11/2017 13:47:0720
10/11/2017 05:21:4620
10/11/2017 08:15:230
10/11/2017 08:42:445
10/11/2017 08:50:20120
10/11/2017 08:58:455
10/11/2017 10:58:110
10/11/2017 11:05:3128
10/11/2017 11:12:1220
11/11/2017 02:53:0780
11/11/2017 13:56:2810
12/11/2017 05:38:200
13/11/2017 18:49:50null
13/11/2017 22:45:4790
14/11/2017 00:40:2020
14/11/2017 06:00:49397
14/11/2017 08:23:2915
14/11/2017 08:27:3120
14/11/2017 08:29:583
14/11/2017 09:19:011
14/11/2017 09:20:282
14/11/2017 09:22:47120
14/11/2017 09:23:5430
14/11/2017 14:02:23null
14/11/2017 15:33:11297
14/11/2017 16:11:0310
14/11/2017 16:57:102
14/11/2017 17:20:275
14/11/2017 20:44:0825
15/11/2017 01:37:540
15/11/2017 10:08:2716
15/11/2017 10:43:4110
15/11/2017 11:21:3060
15/11/2017 14:27:5332
15/11/2017 18:00:0410
15/11/2017 18:33:160
15/11/2017 18:50:595
16/11/2017 06:49:1310
16/11/2017 07:07:540
16/11/2017 09:27:3420
16/11/2017 11:54:4040
16/11/2017 12:02:5310
16/11/2017 13:43:035
18/11/2017 04:35:25null
18/11/2017 09:53:3310
18/11/2017 23:05:08null
21/11/2017 06:54:084
21/11/2017 08:27:1525
21/11/2017 10:18:5630
21/11/2017 12:24:3215
21/11/2017 14:40:5010
21/11/2017 14:53:4510
21/11/2017 18:07:3610
22/11/2017 08:26:545
22/11/2017 14:17:34120
22/11/2017 16:12:0840
22/11/2017 16:12:0940
22/11/2017 17:00:01null
22/11/2017 17:28:14300
22/11/2017 21:20:290
23/11/2017 04:12:1390
23/11/2017 08:28:0335
23/11/2017 14:00:2630
23/11/2017 16:40:4315
23/11/2017 17:01:1660
23/11/2017 18:04:2360
23/11/2017 19:37:552
23/11/2017 22:36:185
24/11/2017 04:24:530
24/11/2017 23:40:5310
25/11/2017 09:26:45100
25/11/2017 11:32:42null
25/11/2017 11:39:14null
25/11/2017 19:51:30null
26/11/2017 11:16:081800
26/11/2017 12:26:0710
26/11/2017 21:53:0775
27/11/2017 06:12:19120
27/11/2017 10:30:0120
27/11/2017 11:00:295
2 ACCEPTED SOLUTIONS
SteveCampbell
Memorable Member
Memorable Member

You could create a date table
Date =  CALENDARAUTO() 

 

then add a month column:
Month = Month(Date[Date])

 

A better practice would be to split the date and time into two columns, one with only the date and one with only the time.

 If you are confident about data integrirty, you can make two new columns in the table containing CREATED and DOWNTIME:

 

Date Only = 'Table 1'[Created]

Change the DATA TYPE of this column to "Date" ( NOT date/Time).

 

Time Only = 'Table 1'[Created]

Change the DATA TYPE of this column to "Time" ( NOT date/Time).

 

Join DATE ONLY to the DATE column in the date table.

 

 

 

You can then create the measure (1440 minutes in a day):

Uptime :=
CALCULATE ( COUNT ( 'Date'[Date] ) * 1440 )
    - CALCULATE ( SUM ( Table1[Downtime (mins)] ) )

 

You can use the month column and uptime column to get the results you need.



Did I answer your question? Mark my post as a solution! Proud to be a Super User!


Connect with me!
Stay up to date on  
Read my blogs on  



View solution in original post

Sorry, This was not clear.

 

 

Beacause your created has time, it cannot be joined to a date table (1/1/18 05:00:00 AM is different to 1/1/18). This new column will return just the date, ignoring the time factor, so it can be joined to a date table.

 

A better practice would be to split the date and time into two columns, one with only the date and one with only the time.

 

If you are confident about data integrirty, you can make two new columns in the table containing CREATED and DOWNTIME:

 

Date Only = 'Table 1'[Created]

Change the DATA TYPE of this column to "Date" ( NOT date/Time).

 

Time Only = 'Table 1'[Created]

Change the DATA TYPE of this column to "Time" ( NOT date/Time).

 

Join DATE ONLY to the DATE column in the date table.

 

 

By seperating, you can also analyze most comon times it goes down. I'll update my solution too.



Did I answer your question? Mark my post as a solution! Proud to be a Super User!


Connect with me!
Stay up to date on  
Read my blogs on  



View solution in original post

3 REPLIES 3
SteveCampbell
Memorable Member
Memorable Member

You could create a date table
Date =  CALENDARAUTO() 

 

then add a month column:
Month = Month(Date[Date])

 

A better practice would be to split the date and time into two columns, one with only the date and one with only the time.

 If you are confident about data integrirty, you can make two new columns in the table containing CREATED and DOWNTIME:

 

Date Only = 'Table 1'[Created]

Change the DATA TYPE of this column to "Date" ( NOT date/Time).

 

Time Only = 'Table 1'[Created]

Change the DATA TYPE of this column to "Time" ( NOT date/Time).

 

Join DATE ONLY to the DATE column in the date table.

 

 

 

You can then create the measure (1440 minutes in a day):

Uptime :=
CALCULATE ( COUNT ( 'Date'[Date] ) * 1440 )
    - CALCULATE ( SUM ( Table1[Downtime (mins)] ) )

 

You can use the month column and uptime column to get the results you need.



Did I answer your question? Mark my post as a solution! Proud to be a Super User!


Connect with me!
Stay up to date on  
Read my blogs on  



Hi Steve,

 

Thanks for the response - I think I'm getting a little lost.

 

If I plot uptime and downtime against Date Only I get this:

 

1.JPG

 

If I plot uptime against Month I get this:

 

2.JPG

 

I wonder if i'm going wrong somewhere around the table joining?

 

What did you mean by 'isolate the date function'?

 

Thanks

 

Chris

Sorry, This was not clear.

 

 

Beacause your created has time, it cannot be joined to a date table (1/1/18 05:00:00 AM is different to 1/1/18). This new column will return just the date, ignoring the time factor, so it can be joined to a date table.

 

A better practice would be to split the date and time into two columns, one with only the date and one with only the time.

 

If you are confident about data integrirty, you can make two new columns in the table containing CREATED and DOWNTIME:

 

Date Only = 'Table 1'[Created]

Change the DATA TYPE of this column to "Date" ( NOT date/Time).

 

Time Only = 'Table 1'[Created]

Change the DATA TYPE of this column to "Time" ( NOT date/Time).

 

Join DATE ONLY to the DATE column in the date table.

 

 

By seperating, you can also analyze most comon times it goes down. I'll update my solution too.



Did I answer your question? Mark my post as a solution! Proud to be a Super User!


Connect with me!
Stay up to date on  
Read my blogs on  



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.