Hello, Been struggling with these. Any assistance or help are appreciated. I have this table which shows timestamp start (start of the downtime) and timestamp end (when downtime finished).We are measuring this downtime by hour. The challenge I have is when i create a chart if theres a downtime that goes over an hour it doesnt got counted with my existing formula. Here's some visual example to make it clear. ABCDF 1TimeStamp - StartTimeStamp - EndMinutesHourMachine 21/18/2020 6:161/18/2020 6:180:02:006Machine 1 31/18/2020 6:171/18/2020 9:173:00:006Machine 1<--- down for 3 hours- meaning , it will be part of the hour 6,7,8,941/18/2020 7:001/18/2020 7:050:05:007Machine 1 51/18/2020 8:001/18/2020 8:070:07:008Machine 2 61/18/2020 8:021/18/2020 8:070:05:008Machine 2 71/18/2020 9:001/18/2020 9:070:07:009Machine 2 81/18/2020 9:001/18/2020 9:100:10:009Machine 3 And here's the results Im expecting but I got stuck and needs help. (Total Minutes is the one im looking for by hour)*i put explanation on where the computation came from. Hope it makes sense. ResultsHourTotal MinutesComputation explanation 6452 + 43 <-- 43 minutes from 3 hours downtime7655 + 60<-- 60 minutes from 3 hours downtime87212 + 60<-- 60 minutes from 3 hours downtime9347 + 10 +17<-- 17 minutes from 3 hours downtime

Forum Discussion

Anonymous

Not applicable

6 years ago

Solved

Need help - Downtime Computation

Hello,

Been struggling with these. Any assistance or help are appreciated.

I have this table which shows timestamp start (start of the downtime) and timestamp end (when downtime finished).

We are measuring this downtime by hour. The challenge I have is when i create a chart if theres a downtime that goes over an hour it doesnt got counted with my existing formula.

Here's some visual example to make it clear.

	A	B	C	D	F
1	TimeStamp - Start	TimeStamp - End	Minutes	Hour	Machine
2	1/18/2020 6:16	1/18/2020 6:18	0:02:00	6	Machine 1
3	1/18/2020 6:17	1/18/2020 9:17	3:00:00	6	Machine 1	<--- down for 3 hours- meaning , it will be part of the hour 6,7,8,9
4	1/18/2020 7:00	1/18/2020 7:05	0:05:00	7	Machine 1
5	1/18/2020 8:00	1/18/2020 8:07	0:07:00	8	Machine 2
6	1/18/2020 8:02	1/18/2020 8:07	0:05:00	8	Machine 2
7	1/18/2020 9:00	1/18/2020 9:07	0:07:00	9	Machine 2
8	1/18/2020 9:00	1/18/2020 9:10	0:10:00	9	Machine 3

And here's the results Im expecting but I got stuck and needs help.
(Total Minutes is the one im looking for by hour)

*i put explanation on where the computation came from. Hope it makes sense.

Results
Hour	Total Minutes	Computation explanation
6	45	2 + 43	<-- 43 minutes from 3 hours downtime
7	65	5 + 60	<-- 60 minutes from 3 hours downtime
8	72	12 + 60	<-- 60 minutes from 3 hours downtime
9	34	7 + 10 +17	<-- 17 minutes from 3 hours downtime

Need Help

rajulshah

6 years ago

Hello Anonymous,

Please use the following M query to get the results:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jc0xCoAwDIXhq0hnC0mqNfUOnqB0EBF08f6jragUW4Jb+MnH816RatU0L9t+rA3GG1kDagKCBuyIdgQoK6caWq+MrIdSu7sm3Qk6PZU61v7Rfabp88dVHeu7bWVNsh4E7arbLtOcafNT41VDOAE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Id = _t, Machine = _t, #"Start Date" = _t, #"End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TimeStamp - Start", type datetime}, {"TimeStamp - End", type datetime}}),
    #"Inserted Time" = Table.AddColumn(#"Changed Type", "Start Time", each Time.From([TimeStamp - Start]), type time),
    #"Inserted Time1" = Table.AddColumn(#"Inserted Time", "End Time", each Time.From([TimeStamp - Start]), type time),
    CreateListOfFullHours = Table.AddColumn(#"Inserted Time1", "AllFullHours", each {Number.From(Text.BeforeDelimiter(Time.ToText(
    Time.From([Start Time])), ":")) .. Number.From(Text.BeforeDelimiter(Time.ToText(
    Time.From([End Time])), ":"))}),
    TransformListOfFullHoursAndSkipFirstHour = Table.AddColumn(CreateListOfFullHours, "SkipFirstHour", each List.Transform(List.Skip([AllFullHours]), each Text.PadStart(Text.From(_), 2, "0") & ":00")),
    CreateNewTable = Table.AddColumn(TransformListOfFullHoursAndSkipFirstHour, "Result", each Table.FromColumns( { {[Start Time]} & [SkipFirstHour], [SkipFirstHour] & {[End Time] } }, { "Start Time", "End Time" } )),
    Cleanup = Table.RemoveColumns(CreateNewTable,{"Start Time", "End Time", "AllFullHours", "SkipFirstHour"}),
    #"Expanded Result" = Table.ExpandTableColumn(Cleanup, "Result", {"Start Time", "End Time"}, {"Start Time", "End Time"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Result",{{"Start Time", type time}, {"End Time", type time}}),
    #"Inserted Start of Hour" = Table.AddColumn(#"Changed Type1", "Start Hour", each Time.StartOfHour([Start Time]), type time)
in
    #"Inserted Start of Hour"

And then you can create a calculated column 'Time Difference' as below:

Time Difference = DATEDIFF(Downtime[Start Time],Downtime[End Time],MINUTE)

And then you can show this column in table as below:

Hope this helps.

5 Replies

amitchandak
Super User
6 years ago
To help you further I need pbix file. If possible please share a sample pbix file after removing sensitive information.Thanks.

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

v-lid-msft

Community Support

6 years ago

Hi Anonymous ,

We can use the following measures after create a calculated table to meet your requirement

Calculated table:

Hour Table =
GENERATESERIES ( 0, 23, 1 )

Measure:

Total Minues = 
VAR hourValue =
    SELECTEDVALUE ( 'Hour Table'[Value] )
VAR hourStart =
    TIME ( hourValue, 0, 0 )
VAR hourEnd =
    TIME ( hourValue + 1, 0, 0 )
RETURN
    SUMX (
        ALLSELECTED ( 'Table' ),
        VAR t =
            CALENDAR ( 'Table'[TimeStamp - Start].[Date], 'Table'[TimeStamp - End].[Date] )
        RETURN
            SUMX (
                t,
                VAR startTime =
                    TIME ( HOUR ( 'Table'[TimeStamp - Start] ), MINUTE ( 'Table'[TimeStamp - Start] ), 0 )
                VAR endTime =
                    TIME ( HOUR ( 'Table'[TimeStamp - End] ), MINUTE ( 'Table'[TimeStamp - End] ), 0 )
                RETURN
                    SWITCH (
                        TRUE (),
                        [Date] <> [TimeStamp - Start].[Date]
                            && [Date] <> [TimeStamp - End].[Date], 60,
                        [Date] = [TimeStamp - Start].[Date]
                            && [Date] = [TimeStamp - End].[Date], SWITCH (
                            TRUE (),
                            hourEnd >= endTime
                                && hourStart <= startTime, DATEDIFF ( startTime, endTime, MINUTE ),
                            hourEnd < startTime
                                || hourStart > endTime, BLANK (),
                            hourEnd <= endTime
                                && hourStart >= startTime, 60,
                            hourEnd >= startTime
                                && hourStart <= startTime, DATEDIFF ( startTime, hourEnd, MINUTE ),
                            hourStart <= endTime
                                && hourEnd >= endTime, DATEDIFF ( hourStart, endTime, MINUTE ),
                            BLANK ()
                        ),
                        [Date] = [TimeStamp - Start].[Date]
                            && [Date] <> [TimeStamp - End].[Date], IF (
                            hourEnd < startTime,
                            BLANK (),
                            IF ( hourStart < startTime, DATEDIFF ( startTime, hourEnd, MINUTE ), 60 )
                        ),
                        [Date] <> [TimeStamp - Start].[Date]
                            && [Date] = [TimeStamp - End].[Date], IF (
                            hourStart > endTime,
                            BLANK (),
                            IF ( hourEnd > endTime, DATEDIFF ( hourStart, endTime, MINUTE ), 60 )
                        ),
                        BLANK ()
                    )
            )
    )

Best regards,

DownTime.pbix60 KB

rajulshah

Resident Rockstar

6 years ago

Hello Anonymous,

Please use the following M query to get the results:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jc0xCoAwDIXhq0hnC0mqNfUOnqB0EBF08f6jragUW4Jb+MnH816RatU0L9t+rA3GG1kDagKCBuyIdgQoK6caWq+MrIdSu7sm3Qk6PZU61v7Rfabp88dVHeu7bWVNsh4E7arbLtOcafNT41VDOAE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Id = _t, Machine = _t, #"Start Date" = _t, #"End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TimeStamp - Start", type datetime}, {"TimeStamp - End", type datetime}}),
    #"Inserted Time" = Table.AddColumn(#"Changed Type", "Start Time", each Time.From([TimeStamp - Start]), type time),
    #"Inserted Time1" = Table.AddColumn(#"Inserted Time", "End Time", each Time.From([TimeStamp - Start]), type time),
    CreateListOfFullHours = Table.AddColumn(#"Inserted Time1", "AllFullHours", each {Number.From(Text.BeforeDelimiter(Time.ToText(
    Time.From([Start Time])), ":")) .. Number.From(Text.BeforeDelimiter(Time.ToText(
    Time.From([End Time])), ":"))}),
    TransformListOfFullHoursAndSkipFirstHour = Table.AddColumn(CreateListOfFullHours, "SkipFirstHour", each List.Transform(List.Skip([AllFullHours]), each Text.PadStart(Text.From(_), 2, "0") & ":00")),
    CreateNewTable = Table.AddColumn(TransformListOfFullHoursAndSkipFirstHour, "Result", each Table.FromColumns( { {[Start Time]} & [SkipFirstHour], [SkipFirstHour] & {[End Time] } }, { "Start Time", "End Time" } )),
    Cleanup = Table.RemoveColumns(CreateNewTable,{"Start Time", "End Time", "AllFullHours", "SkipFirstHour"}),
    #"Expanded Result" = Table.ExpandTableColumn(Cleanup, "Result", {"Start Time", "End Time"}, {"Start Time", "End Time"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Result",{{"Start Time", type time}, {"End Time", type time}}),
    #"Inserted Start of Hour" = Table.AddColumn(#"Changed Type1", "Start Hour", each Time.StartOfHour([Start Time]), type time)
in
    #"Inserted Start of Hour"

And then you can create a calculated column 'Time Difference' as below:

Time Difference = DATEDIFF(Downtime[Start Time],Downtime[End Time],MINUTE)

And then you can show this column in table as below:

Hope this helps.

Anonymous
Not applicable
6 years ago

Hello Rajulshah,
rajulshah

I tried your M Query, but i got error.
Im not really experienced on M, I'm i doing it correctly?

I changed "TimeStamp - Start" and "TimeStamp - End" to
"start_time_local" and "end_time_local"
- rajulshah
  Resident Rockstar
  6 years ago
  Hello Anonymous, were you able to resolve it?
  
  If not, please provide your M Query here.

Forum Discussion

Need help - Downtime Computation

5 Replies

Related content

Workspace Monitoring - compute costs ?

Your Compute session is disconnected

Compute live pool schedule

Uptime Downtime chart by production line, different colors for each downtime reason on the same line

CAGR computation