Forum Discussion
Helper IConsecutive Minutes run and missing days in between
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 |
4 Replies
- tamerj1
Community Champion
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 )
