How to calculate duration of each TASK per ID

[Anonymous]

Hi Guys,

Newbie here, would it be possible to create a measure or calculated column to get the duration of each task per ID ?

The dataset would be like this:

Thank you guys for the help !

[v-frfei-msft]

Hi @Anonymous ,

Here we go:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtE3NNM3MjAyUDC0sDI2UdJRMgRir9Liksw8ICO4JLGoRClWB0WhpZWhKbrCgqL8lNLkksz8PDTVRgZWJhiqc0rzkjPQjTWD2G8EUphaXJyZnIjLAeZWppgq8bnA1BhDOdQJsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Date/Time" = _t, ID = _t, Name = _t, Task = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date/Time", type datetime}, {"ID", Int64.Type}, {"Name", type text}, {"Task", type text}}),
    Partition = Table.Group(Source, {"ID"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Date/Time", "Name", "Task", "Index"}, {"Partition.Date/Time", "Partition.Name", "Partition.Task", "Partition.Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Partition", "Custom", each [Partition.Index]-1),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"ID", "Partition.Index"}, #"Added Custom", {"ID", "Custom"}, "Added Custom", JoinKind.LeftOuter),
    #"Expanded Added Custom" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom", {"Partition.Date/Time"}, {"Added Custom.Partition.Date/Time"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Added Custom", "Custom.1", each if[#"Added Custom.Partition.Date/Time"] = null then [#"Partition.Date/Time"] else [#"Added Custom.Partition.Date/Time"]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Added Custom.Partition.Date/Time"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Partition.Date/Time", type datetime}, {"Custom.1", type datetime}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type1", "Custom.2", each [#"Partition.Date/Time"]-[Custom.1]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom2",{{"Custom.2", type duration}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type2",{{"ID", Order.Ascending}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"Partition.Index", "Custom", "Custom.1"})
in
    #"Removed Columns1"

 

[Anonymous]

Hi,

Thank for this, Im gonna test it first then I'll get back to you after.

Hope this will work.

[nandukrishnavs]

@Anonymous - 

Try the below DAX measure.

Duration =
VAR _Task =
    CALCULATE ( SELECTEDVALUE ( 'Table'[Task] ) )
VAR _ID =
    CALCULATE ( SELECTEDVALUE ( 'Table'[ID] ) )
VAR _Start =
    CALCULATE (
        SELECTEDVALUE ( 'Table'[DATE/TIME] ),
        ALLEXCEPT ( 'Table', 'Table'[ID] ),
        'Table'[Task] = "Start"
    )
VAR _ProductionTime =
    CALCULATE (
        SELECTEDVALUE ( 'Table'[DATE/TIME] ),
        ALLEXCEPT ( 'Table', 'Table'[ID] ),
        'Table'[Task] = "Production"
    )
VAR _End =
    CALCULATE (
        SELECTEDVALUE ( 'Table'[DATE/TIME] ),
        ALLEXCEPT ( 'Table', 'Table'[ID] ),
        'Table'[Task] = "End"
    )
VAR _ProdDuration =
    DATEDIFF ( _Start, _ProductionTime, MINUTE )
VAR _EndDuration =
    DATEDIFF ( _ProductionTime, _End, MINUTE )
VAR result =
    SWITCH ( _Task, "Start", 0, "Production", _ProdDuration, "End", _EndDuration )
RETURN
    result

If you find this solution useful, please mark this as an accepted solution.

Regards,

Nandu Krishna

[Anonymous]

Hi @nandukrishnavs,

Is this also applicable if I add another task like MEETING and there would be times that MEETIING would be the first task than LUNCH and or LUNCH would be the first task than MEETING?

[nandukrishnavs · Accepted solution]

@Anonymous  - If you have multiple tasks and if it goes through proper order, then you can try the below DAX measure.

Duration =
VAR _CurrentTaskTime =
    CALCULATE ( SELECTEDVALUE ( 'Table'[DATE/TIME] ) )
VAR _PreviousTaskTime =
    CALCULATE (
        MAX ( 'Table'[DATE/TIME] ),
        ALLEXCEPT ( 'Table', 'Table'[ID] ),
        'Table'[DATE/TIME] < _CurrentTaskTime
    )
VAR _timeDifference_IN_Minutes =
    IF (
        ISBLANK ( _PreviousTaskTime ),
        0,
        DATEDIFF ( _PreviousTaskTime, _CurrentTaskTime, MINUTE )
    )
RETURN
    _timeDifference_IN_Minutes

[TJ19]

Hi Nandu,

I am working on a similar measure. Thank you for the solution it worked perfectly for me.

Also, I need to calculate average of the total duration of the ID for every month as a KPI.

Can you please help how to calculate average on a DAX measure?

Thank you,

Teja

[Anonymous]

Hi @nandukrishnavs ,

This works really fine, however, when another user ID stamps another task, the duration is still counting. It should be back to 00:00:00 or the timer should start from the last stamp per userID.

Please see below screenshot:

In above screenshot, notice that the ID is different but the measure still counts from the last stamp which is a different ID.

[Anonymous]

Warning
---------
Please do not do this with a complex measure as suggested above. You'll be sorry rather sooner than later (trust me).

Please use Power Query to prepare the data upfront.

If you ignore this, please don't come back and don't tell me you have not been fore-warned 🙂

Best
D

[Anonymous]

hi @Anonymous ,

I can't use power query as I am using streaming dataset for this.

[Anonymous]

Hm... Interesting. You say you can't use PQ and the marked answer to your question contains PQ code. I really find it weird and funny at the same time 🙂

Best
D

[Anonymous]

Hi @Anonymous ,

Oh sorry, I think I've marked the wrong solution. My mistake. Thanks for noticing.

[amitchandak]

@Anonymous , what is the expected output

[Anonymous]

The output would be like this.

[Anonymous]

This must be a calculated column and should be performed in Power Query, not DAX. Once the model has a table with tasks and their durations, you can start writing measures.

And here's how to build correct models where DAX is SIMPLE to write:

https://www.youtube.com/watch?v=78d6mwR8Gt

https://www.youtube.com/watch?v=_quTwyvDfG0

Best
D