Forum Discussion

joep78's avatar
joep78
Icon for Helper III rankHelper III
6 years ago
Solved

DateDiff between rows based on an index and a filter

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!

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

4 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      joep78
      Icon for Helper III rankHelper III
      6 years ago

      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?

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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