How can I measure the time spent on each state

[Anonymous]

Hi, 

I have the following data - each ID spends some time on different states that can reoccur over time. I need to calculate the time overall time spent on each state by each ID. Any suggestions will be much appreciated. Thank you.

1 - time spent on V, time spent on C, 

2 - time spent on V, time spent on C

ID	Timestamp	State
1	8/10/2020	V
2	8/11/2020	C
1	8/12/2020	C
2	8/13/2020	D
1	8/14/2020	C
2	8/15/2020	V
1	8/16/2020	C
1	8/17/2020	C
2	8/18/2020	V
2	8/19/2020	V

[v-juanli-msft]

Hi @Anonymous 

Create a measure

Measure =
VAR mind =
    CALCULATE (
        MIN ( 'Table'[Timestamp] ),
        ALLEXCEPT ( 'Table', 'Table'[ID], 'Table'[State] )
    )
VAR maxd =
    CALCULATE (
        MAX ( 'Table'[Timestamp] ),
        ALLEXCEPT ( 'Table', 'Table'[ID], 'Table'[State] )
    )
RETURN
    DATEDIFF ( mind, maxd, DAY ) + 1

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , You can have diff between two rows  as a new column

datediff(maxx(filter(Table, [Timestamp] <[Timestamp] ),[Timestamp]),[Timestamp],Minute)

You can use that for more calculations