topic Re: Consecutive Minutes run and missing days in between in DAX Commands and Tips

Consecutive Minutes run and missing days in between

rogerthat — Mon, 27 Mar 2023 14:30:38 GMT

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

Re: Consecutive Minutes run and missing days in between

tamerj1 — Mon, 27 Mar 2023 15:49:58 GMT

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 )

Re: Consecutive Minutes run and missing days in between

rogerthat — Mon, 27 Mar 2023 18:16:33 GMT

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

Re: Consecutive Minutes run and missing days in between

tamerj1 — Tue, 28 Mar 2023 05:57:46 GMT

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

Re: Consecutive Minutes run and missing days in between

rogerthat — Tue, 28 Mar 2023 13:52:05 GMT

Calculated column.