Forum Discussion
Create an Uptime Column
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.
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.
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.