Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
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 )
@tamerj1 - I attempted the above but they aren't giving correct minutes or days...Not sure
@rogerthat
Are ayou creating a calculated column or a measure? If a measure, then please advise what do you have in the filter context.
Calculated column.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
52 | |
46 | |
15 | |
12 |