CODEWITHSUNDEEP
dax
test12345
test12345

Reputation: 53

How to get DateTime difference in Dax?

I have table which has an a DateTime column, status column and an index column, I am trying to get a column which shows the time difference between the previous row and the current row and for it to ignore the previous row status column is blank.

This is what the table looks like, currently:

Time                     ID        status.     timesinceprev(seconds)          index 
22.1.21 04:02:04         12         low                  0                       1
22.1.21 04:24:07         12         low                1320                      2
22.1.21 04:26:04         12         medium             120                       3
22.1.21 04:29:04         12                             180                      4
22.1.21 04:30:05         12                             61                       5

I want to change the timeSinceprev to show the time difference in the format HH:MM:SS when the previous row status column is blank, Here is what I have currently with the query:

timeCol = 
var tempcol=
MINX(FILTER('Table',
'Table'[ID]=EARLIER('Table'[ID])
),'Table'[Time])
 
var filtertemp =
 EARLIER('Table'[status])
 
 
RETURN IF(filtertemp<>BLANK(),FORMAT('Table'[Time]-tempcol,"HH:MM:SS"))

Upvotes: 0

Views: 108

Answers (1)

smpa01
smpa01

Reputation: 4346

Use EARLIER like following in combination with ID and Index to get the previous row value when status=blank, grouped by ID

Column = 
    VAR _1 =
        SWITCH (
            TRUE (),
            'Table'[status] = BLANK (),
                MAXX (
                    FILTER (
                        'Table',
                        'Table'[ID] = EARLIER ( 'Table'[ID] )
                            && 'Table'[Index] < EARLIER ( 'Table'[Index] )
                    ),
                    'Table'[Time]
                )
        )
    VAR _2 =
        SWITCH (
            TRUE (),
            'Table'[status] = BLANK (), CALCULATE ( MAX ( 'Table'[Time] ) )
        )
    VAR _3 =
        FORMAT ( _2 - _1, "HH:MM:SS" )
    RETURN
        _3

Solution

Upvotes: 1

Related Questions