Forum Discussion
Tracking duration between Status
I don't know why I'm having issues like this its I read the forums, watch the youtube videos and its still not hitting home for me. Ugh here is my latest struggle maybe someone can help me out with this.
my goal is to track the time between statuses, I have a spreadsheet that contains;
AssetUUID
Datetime
Status
1250 | 09/05/2021 2:00AM | SOS |
1250 | 09/05/2021 2:45AM | SOS Cancel |
15808 | 09/01/2021 12:45PM | Check in |
15808 | 09/01/2021 5:55PM | Check out |
and I need to calculate how long it took takes between Status changes.
I've tried these samples.
status time =
GENERATE('User Data',
VAR _curUserID = 'User Data'[assetUUID]
VAR _startperiod = 'User Data'[timeHappenedUTC]
VAR _curRankedIndex = 'User Data'[RankedIndex]
VAR _nextRankedIndex = IF(COUNTROWS(FILTER(ALL('User Data'), 'User Data'[RankedIndex] > _curRankedIndex)) > 0, _curRankedIndex + 1, _curRankedIndex)
VAR _endperiod = CALCULATE(MAX('User Data'[timeHappenedUTC]), FILTER(ALL('User Data'), 'User Data'[RankedIndex] = _nextRankedIndex && 'User Data'[assetUUID] = _curUserID))
RETURN
GENERATESERIES(_startperiod, _endperiod, TIME(0,1,0)))
and I have tried
Event =
VAR CurID = 'User Data'[assetUUID]
VAR CurTitle = 'User Data'[Title]
VAR CurDate = 'User Data'[timeHappenedUTC]
VAR MaxDate =
MAXX (
FILTER (
'User Data',
'User Data'[assetUUID]= CurID
&& 'User Data'[timeHappenedUTC] < CurDate
&& 'User Data'[Title] = "SOS Canceled"
),
'User Data'[timeHappenedUTC]
)
VAR MinDate =
IF (
MaxDate = BLANK (),
MINX ( FILTER ( 'User Data', 'User Data'[assetUUID] = CurID ), 'User Data'[timeHappenedUTC] ),
MINX (
FILTER ( 'User Data', 'User Data'[assetUUID] = CurID && 'User Data'[timeHappenedUTC] > MaxDate ),
'User Data'[timeHappenedUTC]
)
)
RETURN
IF ( CurBenched = "Not Benched", BLANK (), DATEDIFF (
but I keep receiving errors that I just can't seem to get myself out of any advice would be fantastic
