Time status

[Anonymous]

Hi all,

I have data that looks like this:

Date	Time	Status
1899-10-12	02:40:00	M
1899-10-12	02:40:50	M
1899-10-12	02:41:21	X
1899-10-12	02:41:56	X
1899-10-12	02:42:34	X
1899-10-12	02:43:01	M
1899-10-12	02:44:21	M
1899-10-12	02:44:59	X
1899-10-12	02:45:46	X
1899-10-12	02:46:38	M

Now what I want to do is to calculate how much time the status was "X" and so I would need to calculate the time between the first record that has X till the last record that has X with no other status then X between them. So for example in my dummy data you can see that this happens 2 times: 02:41:21 until --> 02:42:34 And 02:44:59 until --> 02:45:46.

So my expected result would be: 00:01:13 + 00:00:47 = 00:02:00 X status

I hope the above is understandable, thanks in advance !

Regards,

L.Meijdam

[MarcelBeug · Accepted solution]

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).