sum of Time in state

[fmozingo]

I have a table of data formatted like below, and I would like to be able to slice and dice the data to get total time in state depending on the slicers (Bassically Equip by date range).

- StateChangeId is non-duplicated primary key.

- EquipmentId is duplicated id of specific equipment.

- StateId is a duplicated state or status ( like broken, running, etc)

- and CreatedDate is the Date / Time the state was created.

Obviously when one state(record) starts the previous state(record) stops giving duration.

initially I used PowerQuery to group by equipId and create a per group index which I then used to bring over the data from the previous record and and calculate the duration. This worked except the beginning where there is no previous record and the end where the data falls shot of the end of the day or a state lasts several days or longer. This also does not seem to be accurate when slicing.

I Think this better option would be to use DAX and measures which I have been researching but just cant seem to get quite right. 

StateChangeId	EquipId	StateId	CreatedDate
1	10	50	5/14/2022 10:42
2	13	55	5/14/2022 17:00
3	12	50	5/14/2022 17:00
4	13	50	5/14/2022 17:05
5	10	45	5/14/2022 17:11
6	10	50	5/14/2022 17:37

If someone could point me in the right direction I would greatly appreciate it.

[ChrisMendoza · Accepted solution]

@fmozingo - So you can see the similar solution in Power Query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc5BCsAwCATArxTPgbhGK+QrIf//RqvQQEl72csOq2MQqBD4Dsuo0CoscoC7Cs0ySEK0KO0tvDOniBLysbGEro1dWAp7/tDtCpDi/PvUe3Oa8wI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StateChangeId = _t, EquipId = _t, StateId = _t, CreatedDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"StateChangeId", Int64.Type}, {"EquipId", Int64.Type}, {"StateId", Int64.Type}, {"CreatedDate", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"EquipId"}, {{"Grouped", each _, type table [StateChangeId=nullable number, EquipId=nullable number, StateId=nullable number, CreatedDate=nullable datetime]}, {"MaxDate", each List.Max([CreatedDate]), type nullable datetime}, {"MinDate", each List.Min([CreatedDate]), type nullable datetime}}),
    #"Expanded Grouped" = Table.ExpandTableColumn(#"Grouped Rows", "Grouped", {"StateChangeId", "StateId", "CreatedDate"}, {"StateChangeId", "StateId", "CreatedDate"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Grouped", "Total Minutes", each if [CreatedDate] = [MaxDate] then Duration.TotalMinutes([MaxDate]-[MinDate]) else 0),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"MaxDate", "MinDate"})
in
    #"Removed Columns"

[rohit_singh]

Hello @fmozingo ,

Please try the following :

1) On your table, create a calculated column to fetch previous datetime for the same equipment id.

PrevStateDate =

CALCULATE(

MAX(StateChange[CreatedDate]),

FILTER(ALLEXCEPT(StateChange, StateChange[EquipId]),

StateChange[EquipId] = EARLIER(StateChange[EquipId]) &&

StateChange[StateChangeId] < EARLIER(StateChange[StateChangeId])

)

)

2) Next, add another calculated column to calculate duration.

Duration (Mins) =

var _duration = DATEDIFF(StateChange[PrevStateDate],StateChange[CreatedDate], MINUTE)

RETURN

IF(ISBLANK(_duration), 0, _duration)

3) Add equipmentid and duration columns to a table visual and get the expected outcome

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊