Time status

Revised solution below. You can create a new - blank - query, go to the advanced editor and replace the default code by the code below.

Remark: the first step was the result of using option "Enter Data". If you want to adjust data, you can use the gear button at the right from "Source" in the "Applied Steps" pane at the right hand side of the query window.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc5LCoAwDATQu2RdMN+qcwf3Qun9r2GhGwNWyGLgkUxaI5FNx7DsVIgVzmAe8aJepmrWyJp2BSoj3t+7gqhJ7a0K87UaWFJvUp+9S41zfTngP19V2DEv9wc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Time = _t, Status = _t]),
    Typed = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Time", type time}, {"Status", type text}}),
    AddedDateTime = Table.AddColumn(Typed, "DateTime", each [Date] & [Time], type datetime),
    Grouped1 = Table.Group(AddedDateTime,
                           {"Status"},
                           {{"First", each List.Min([DateTime]), type datetime},
                            {"Last",  each List.Max([DateTime]), type datetime}},
                           GroupKind.Local),
    AddedFirstDate = Table.AddColumn(Grouped1, "First Date", each DateTime.Date([First]), type date),
    AddedLastDate = Table.AddColumn(AddedFirstDate, "Last Date", each DateTime.Date([Last]), type date),
    #"Added Custom" = Table.AddColumn(AddedLastDate, "Date", each List.Dates([First Date],Duration.Days([Last Date]-[First Date])+1,#duration(1,0,0,0)), type {date}),
    #"Expanded Date" = Table.ExpandListColumn(#"Added Custom", "Date"),
    AddedStart = Table.AddColumn(#"Expanded Date", "Start", each if [Date] = [First Date] then [First] else [Date] & #time(0,0,0), type datetime),
    AddedEnd = Table.AddColumn(AddedStart, "End", each if [Date] = [Last Date] then [Last] else [Date] & #time(23,59,59.9999999), type datetime),
    AddedDuration = Table.AddColumn(AddedEnd, "Duration", each [End] - [Start], type duration),
    SelectedColumns = Table.SelectColumns(AddedDuration,{"Status", "Date", "Duration"})
in
    SelectedColumns

The result is a table with 1 row per status/date. This will allow you to filter on status and/or dates in your visualizations.

Also the total durations - these are decimal values in the data model - need to be calculated in your visuals - or with DAX - as these depend on filter context.

I don't know if the (total) durations can be formatted as (h):mm:ss: that would require some DAX beyond my knowledge.

My advice would be to raise a new topic, specifically for "Totalling decimal values in DAX and have the results displayed as {h}:mm:ss". (Please use similar text as the topic title. "Time status" won't win the best topic title award).

Hi Anonymous

Please see the attached file

Using your Sample Data

Here are the steps

Step#1: RANK by time

Add a calculated column which will RANK based on TIME column

RANK =
RANKX ( TableName, TableName[Time],, asc, DENSE )

Step 2: Identify Starting and End Points for "X"

Using this calculated Column

Previous/Next Status Is X =
IF (
    TableName[Status] = "X"
        && CALCULATE (
            VALUES ( TableName[Status] ),
            FILTER ( ALL ( TableName ), TableName[RANK] = EARLIER ( TableName[RANK] ) - 1 )
        )
            <> "X",
    "Starting Point",
    IF (
        TableName[Status] = "X"
            && CALCULATE (
                VALUES ( TableName[Status] ),
                FILTER ( ALL ( TableName ), TableName[RANK] = EARLIER ( TableName[RANK] ) + 1 )
            )
                <> "X",
        "End Point"
    )
)

Step# 3: Compute the Time Difference

Diff =
IF (
    TableName[Previous/Next Status Is X] = "End Point",
    DATEDIFF (
        MAXX (
            FILTER (
                TableName,
                TableName[RANK] < EARLIER ( TableName[RANK] )
                    && TableName[Previous/Next Status Is X] = "Starting Point"
            ),
            TableName[Time]
        ),
        TableName[Time],
        SECOND
    )
)