DateDiff between rows based on an index and a filter

[joep78]

Hello all,

I have one issue with a datediff between rows, based on an index and a filter. 

I want to check the time between 2 status transactions. 

I use the following DAX:

Dif between previous index =

VAR currentOrder = [OrderRef]

VAR currentIndex = [Index]

VAR PreviousDatetime =

CALCULATE (

MIN ( OrderHistory[ModificationDateTime] );

FILTER (

'OrderHistory';

'OrderHistory'[OrderRef] = currentOrder

&& 'OrderHistory'[Index]

= currentIndex -1

)

)

RETURN

DATEDIFF ( [ModificationDateTime]; PreviousDatetime; MINUTE )*-1

my output is as follow:

Now I only want to check the DateDiff between those rows where the UserFieldName = Status. I did it with the following change in the DAX:

Dif between previous index =

VAR currentOrder = [OrderRef]

VAR currentIndex = [Index]

VAR PreviousDatetime =

CALCULATE (

MIN ( OrderHistory[ModificationDateTime] );

FILTER (

'OrderHistory';

'OrderHistory'[OrderRef] = currentOrder

&& 'OrderHistory'[Index]

= currentIndex -1

)

)

RETURN

DATEDIFF ( [ModificationDateTime]; PreviousDatetime; MINUTE )*-1

Now the output is as follow:

still not the wanted output because my final row does not contains a value (this is logical given my DAX). 

What options do I have to validate the time between index 1 and index 4 (so those items with UserFieldName =Status). 

thanks in advance for your comment!

[MFelix]

Hi @joep78 ,

Try simply to use a IF statment before the calculation redo your calculated column to:

Dif between previous index _ =
VAR currentOrder = [OrderRef]
VAR currentIndex = [Index]
VAR PreviousDatetime =
    CALCULATE (
        MIN ( OrderHistory[ModificationDateTime] );
        FILTER (
            'OrderHistory';
            'OrderHistory'[OrderRef] = currentOrder
                && 'OrderHistory'[Index] = currentIndex - 1
        )
    )
RETURN
    IF (
        OrderHistory[UserFieldName] = "Status";
        DATEDIFF ( [ModificationDateTime]; PreviousDatetime; MINUTE ) * -1;
        BLANK ()
    )

On my test return the correct result.

[joep78]

Hi Miguel,

Thanks for your reply. my output based on your solution(but another example with more addition rows) is as follow:

So now only those rows where UserFieldName = Status does contains a value, but the value in it is not based on the previous column where UserFieldName = Status but on the previous row. In this case this row should have a value of 1 (the previous row with UserFieldName = Status was 1 minute earlier). Does this make any sense?

[v-yiruan-msft · Accepted solution]

Hi @joep78 ,

Please update the formula of related calculated column as below and check if it can get your desired result:

Dif between previous index = 
VAR _premDate =
    CALCULATE (
        MAX ( 'OrderHistory'[ModificationDateTime] ),
        FILTER (
            'OrderHistory',
            OrderHistory[OrderRef] = EARLIER ( 'OrderHistory'[OrderRef] )
                && 'OrderHistory'[UserFieldName] = "Status"
                && 'OrderHistory'[UserFieldName] = EARLIER ( 'OrderHistory'[UserFieldName] )
                && 'OrderHistory'[Index] < EARLIER ( 'OrderHistory'[Index] )
        )
    )
RETURN
    DATEDIFF ( _premDate, 'OrderHistory'[ModificationDateTime], MINUTE )

Best Regards

Rena

[joep78]

Hi Rena & Miguel,

Thanks for your support, with the final adjustment of Rena my solution gives the correct output. Kudos for both of you!