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

[MarcelBeug]

This can be done in Power Query, using Group By and adjust the generated code by adding parameter GroupKind.Local, which will group the data by consecutive values.

let
    Source = Data,
    AddedDateTime = Table.AddColumn(Source, "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),
    Filtered = Table.SelectRows(Grouped1, each ([Status] = "X")),
    AddedDuration = Table.AddColumn(Filtered, "Duration", each [Last] - [First], type duration),
    Grouped2 = Table.Group(AddedDuration,
                           {"Status"},
                           {{"TotalDuration", each List.Sum([Duration]), type duration}})
in
    Grouped2

[Anonymous]

Hi @MarcelBeug,

Since I was/am a complete stranger to Power Query I was a bit reticent about using it. Although I decided to try to use your code provided, it looks like it gets really close to what I want to achieve. Except it is returning the following error:

Expression.Error: We cannot apply operator - to types Text and Text.
Details:
Operator=-
Left=1899-10-1202:42:34
Right=1899-10-1202:41:21

Another question I had was, in this approach is it also possible to keep the function to filter on a single day or date ? Or does this just return the totalduration for the entire file dataset ? Since I'd like to keep the filter option.

Best regards,

L.Meijdam

[MarcelBeug]

The date and time column must be in date and time format respectively, before using my code.

In that case, the concatenation of date and time will give a datetime field.

In your case it's just text.

The solution returns the duration for consecutive values, as requested, not per date.

If you want to filter by date, then the solution must be completely revised.

E.g. split at midnights, or will there also be additional requirements to limit the results to working hours and exclude weekends and holidays?

[Anonymous]

@MarcelBeug,

As of the datetime problem, my data was in date/time format. In the step "AddedDuration". the column Duration gets the data format "Duration". Changing the datatype there to date/time doesnt solve the error.

Ultimately my prefered data format to get the duration in is in HH:MM:SS

[MarcelBeug]

Your data is NOT in date/time format. That is a mistake.

Note that actual values may differ from the column types, e.g. you can have text values in a colum with date/time type.

Later today will prove that my solution works if dates and times are really in date and time format.

2 questions:

1. Why are your dates in 1899?

2. May the resulting durations exceed 24 hours?

[Anonymous]

Hi @MarcelBeug,

I did'nt know there could be text values in a column that is specified as a date column. This is how my sample dataset looks before your code is inserted: 

1. I putted 1899 in them as an example this is not my real dataset if it is inconvenient another different year can be used instead

2. The max duration that can be achieved for 1 day is 24 hours. If it is wished to show the total of multiple days it can exceed 24 hours. I'd like it to be displayed as for example 33:22:22, if that is possible. 

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

[Anonymous]

Hi @MarcelBeug,

Thanks for your time, this worked for me. As for the problem of displaying it in H:mm:ss format I'll figure something out (and I'll keep your title feedback in mind)

Best regards,

L.Meijdam

[Zubair_Muhammad]

Hi @Anonymous

You can do this in DAX as well.

Are you interested in seeing the DAX solution?

[Anonymous]

Hi @Zubair_Muhammad,

Yes I would be very interested in a DAX solution aswell !

Best regards,

L.Meijdam

[Zubair_Muhammad · Accepted solution]

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

[Anonymous]

Hi @Zubair_Muhammad,

I tested it with my dummy table and that works fine, but in my actual dataset the 2nd calculation "Previous/Next Status Is X" returns "A table of multiple  values was supplied where a single value was expected."

In my actual dataset there are more than 2 different [status] types .. is that the reason I get this error ? I thought that would'nt be a problem I'm sorry

Regards,

L.Meijdam

[Zubair_Muhammad]

Hi @Anonymous

Could you share your file?

May be I will learn something new

[Zubair_Muhammad]

Hi @Anonymous

One reason could be that there are multiple Status with exact same time

[Anonymous]

Hi @Zubair_Muhammad,

I just checked my dataset and you were correct. There are quite alot of records that have the same time and status, could this perhaps be fixed by transforming "time" to a "date/time" Format ?

[Zubair_Muhammad]

Hi @Anonymous

I am not sure about transforming "time" to a "date/time" Format.

But alternatively,

Instead of RANK calculated Column (which gives us duplicate RANKs due to same times)

We can add an Index Column from Power Query>>AddColumn>>Index Column>>> from 1

Rename this New Column to RANK

I think then "Previous/Next Status Is X" will work

But your data should be sorted in Ascending Order by time

[Anonymous]

Hi @Zubair_Muhammad,

Thanks this totally fixed the problem ! everything is working like expected, really like the DAX solution this allows me to keep my filtering options ! 

Best regards,

L.Meijdam

[Anonymous]

Hi @MarcelBeug,

Thanks for your quick response, the timeduration for every day (24 hours) would be what i'd like to achieve. I want the value to be in a card visual where it shows the sum of all duration by default, and where it can be filtered by day. Weekends and holidays dont need to be taken into account.

Best regards,

L.Meijdam 

[Anonymous]

Hi @MarcelBeug,

Thanks for your quick response, but if possible i'd like to achieve my outcome through a DAX formula not through Power Query.

Regards,

L.Meijdam

[Anonymous]

Another thing is that I want to create 2 different functions for 2 seperate "Status" values. In my real dataset there are more than 2 "Status" values. That is why it would be convenient if it would be possible through a measure for example, since that would be dynamic aswell. I also need to be able to filter on "Date" in the future.