Consecutive Minutes run and missing days in between

[rogerthat]

Hi,

I'm trying to create the 'CONSECUTIVE MINUTES' column and the 'MISSING DAYS STATUS'

CONSECUTIVE MINUTES IS IF THE STATUS REMAINS THE SAME WITHIN THE DAY FOR THE SAME ID, and counting the minutes from the last status if consecutive.

'MISSING DAYS STATUS' should be days missing in between any of the status changes. 

I'm having a difficulty with this piece and tried using similar approaches.

ID	DATTIME	TIME	STATUS	CONSECUTIVE MINUTES	MISSING DAYS STATUS
XYZ	3/13/2023	2:00PM	COMPLETED
XYZ	3/13/2023	3:30PM	COMPLETED	90
BYZ	3/14/2023	9:00AM	INCOMPLETE
BYZ	3/14/2023	11:00AM	INCOMPLETE	120
XYZ	3/15/2023	5:00PM	INCOMPLETE		2
BYZ	3/17/2023	9:00AM	COMPLETED		3

[tamerj1]

Hi @rogerthat 

please try

CONSECUTIVE MINUTES =
VAR CurrentDateTime = 'Table'[DateTime]
VAR CurrentIDDateTimes =
CALCULATETABLE (
VALUES ( 'Table'[DateTime] ),
ALLEXCEPT ( 'Table', 'Table'[ID] )
)
VAR PreviousDateTimes =
FILTER ( CurrentIDDateTimes, 'Table'[DateTime] < CurrentDateTime )
VAR PreviousDateTime =
COALESCE ( MAXX ( PreviousDateTimes, 'Table'[DateTime] ), CurrentDateTime )
VAR Minutes =
DATEDIFF ( PreviousDateTime, CurrentDateTime, MINUTE )
RETURN
IF ( Minutes < 1440, Minutes )

MISSING DAYS STATUS =
VAR CurrentDateTime = 'Table'[DateTime]
VAR CurrentIDDateTimes =
CALCULATETABLE (
VALUES ( 'Table'[DateTime] ),
ALLEXCEPT ( 'Table', 'Table'[ID] )
)
VAR PreviousDateTimes =
FILTER ( CurrentIDDateTimes, 'Table'[DateTime] < CurrentDateTime )
VAR PreviousDateTime =
COALESCE ( MAXX ( PreviousDateTimes, 'Table'[DateTime] ), CurrentDateTime )
VAR Hours =
DATEDIFF ( PreviousDateTimes, CurrentDateTime, HOUR )
VAR Days =
DATEDIFF ( PreviousDateTime, CurrentDateTime, DAY )
RETURN
IF ( Hours > 24, Days )

[rogerthat]

@tamerj1 - I attempted the above but they aren't giving correct minutes or days...Not sure

[tamerj1]

@rogerthat 
Are ayou creating a calculated column or a measure? If a measure, then please advise what do you have in the filter context.

[rogerthat]

Calculated column.