Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:53 | 600 |
31/10/2017 10:05:55 | 30 |
31/10/2017 15:10:33 | 0 |
31/10/2017 20:00:09 | 30 |
01/11/2017 06:35:01 | 45 |
01/11/2017 06:47:45 | 0 |
01/11/2017 15:11:30 | 15 |
01/11/2017 17:41:26 | 2 |
01/11/2017 18:52:17 | 15 |
01/11/2017 21:28:17 | 15 |
02/11/2017 07:24:04 | 10 |
02/11/2017 11:52:51 | 10 |
03/11/2017 19:19:14 | null |
03/11/2017 20:58:53 | 25 |
03/11/2017 21:03:02 | 10 |
03/11/2017 21:05:47 | 10 |
04/11/2017 05:20:01 | 15 |
04/11/2017 05:28:24 | 15 |
04/11/2017 05:45:35 | 60 |
04/11/2017 05:53:49 | 20 |
04/11/2017 06:13:48 | 180 |
04/11/2017 06:25:20 | 40 |
04/11/2017 09:23:45 | 0 |
04/11/2017 12:12:04 | 210 |
04/11/2017 12:47:09 | 30 |
04/11/2017 14:30:43 | 131 |
05/11/2017 01:03:15 | 0 |
05/11/2017 01:13:47 | 0 |
05/11/2017 17:00:56 | 90 |
06/11/2017 08:03:16 | 19 |
06/11/2017 08:08:20 | 35 |
06/11/2017 08:31:25 | 8 |
06/11/2017 12:52:19 | 0 |
06/11/2017 14:22:47 | 10 |
06/11/2017 21:59:36 | 0 |
07/11/2017 22:12:50 | 30 |
08/11/2017 01:01:16 | 180 |
08/11/2017 07:19:02 | null |
08/11/2017 09:51:16 | 30 |
08/11/2017 12:09:26 | 10 |
08/11/2017 14:59:55 | 10 |
08/11/2017 15:23:11 | 90 |
09/11/2017 08:01:24 | 60 |
09/11/2017 09:49:56 | 30 |
09/11/2017 09:55:59 | 30 |
09/11/2017 13:47:07 | 20 |
10/11/2017 05:21:46 | 20 |
10/11/2017 08:15:23 | 0 |
10/11/2017 08:42:44 | 5 |
10/11/2017 08:50:20 | 120 |
10/11/2017 08:58:45 | 5 |
10/11/2017 10:58:11 | 0 |
10/11/2017 11:05:31 | 28 |
10/11/2017 11:12:12 | 20 |
11/11/2017 02:53:07 | 80 |
11/11/2017 13:56:28 | 10 |
12/11/2017 05:38:20 | 0 |
13/11/2017 18:49:50 | null |
13/11/2017 22:45:47 | 90 |
14/11/2017 00:40:20 | 20 |
14/11/2017 06:00:49 | 397 |
14/11/2017 08:23:29 | 15 |
14/11/2017 08:27:31 | 20 |
14/11/2017 08:29:58 | 3 |
14/11/2017 09:19:01 | 1 |
14/11/2017 09:20:28 | 2 |
14/11/2017 09:22:47 | 120 |
14/11/2017 09:23:54 | 30 |
14/11/2017 14:02:23 | null |
14/11/2017 15:33:11 | 297 |
14/11/2017 16:11:03 | 10 |
14/11/2017 16:57:10 | 2 |
14/11/2017 17:20:27 | 5 |
14/11/2017 20:44:08 | 25 |
15/11/2017 01:37:54 | 0 |
15/11/2017 10:08:27 | 16 |
15/11/2017 10:43:41 | 10 |
15/11/2017 11:21:30 | 60 |
15/11/2017 14:27:53 | 32 |
15/11/2017 18:00:04 | 10 |
15/11/2017 18:33:16 | 0 |
15/11/2017 18:50:59 | 5 |
16/11/2017 06:49:13 | 10 |
16/11/2017 07:07:54 | 0 |
16/11/2017 09:27:34 | 20 |
16/11/2017 11:54:40 | 40 |
16/11/2017 12:02:53 | 10 |
16/11/2017 13:43:03 | 5 |
18/11/2017 04:35:25 | null |
18/11/2017 09:53:33 | 10 |
18/11/2017 23:05:08 | null |
21/11/2017 06:54:08 | 4 |
21/11/2017 08:27:15 | 25 |
21/11/2017 10:18:56 | 30 |
21/11/2017 12:24:32 | 15 |
21/11/2017 14:40:50 | 10 |
21/11/2017 14:53:45 | 10 |
21/11/2017 18:07:36 | 10 |
22/11/2017 08:26:54 | 5 |
22/11/2017 14:17:34 | 120 |
22/11/2017 16:12:08 | 40 |
22/11/2017 16:12:09 | 40 |
22/11/2017 17:00:01 | null |
22/11/2017 17:28:14 | 300 |
22/11/2017 21:20:29 | 0 |
23/11/2017 04:12:13 | 90 |
23/11/2017 08:28:03 | 35 |
23/11/2017 14:00:26 | 30 |
23/11/2017 16:40:43 | 15 |
23/11/2017 17:01:16 | 60 |
23/11/2017 18:04:23 | 60 |
23/11/2017 19:37:55 | 2 |
23/11/2017 22:36:18 | 5 |
24/11/2017 04:24:53 | 0 |
24/11/2017 23:40:53 | 10 |
25/11/2017 09:26:45 | 100 |
25/11/2017 11:32:42 | null |
25/11/2017 11:39:14 | null |
25/11/2017 19:51:30 | null |
26/11/2017 11:16:08 | 1800 |
26/11/2017 12:26:07 | 10 |
26/11/2017 21:53:07 | 75 |
27/11/2017 06:12:19 | 120 |
27/11/2017 10:30:01 | 20 |
27/11/2017 11:00:29 | 5 |
Solved! Go to Solution.
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
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
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:
If I plot uptime against Month I get this:
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
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |