Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Calculate Days Between Two Rows Based on Conditions from Other Columns

I have a table called "Productivity".  In this table, I am attempting to calculate the number of days between "TRIAG" and "DIAGNOSIS", when the serial numbers match.  Any ideas?

  • AnthonyJoseph's avatar
    AnthonyJoseph
    3 years ago

    Hi Anonymous 

     

    can you try this code:

    days diff = 
var _dia = 'Table data'[dated]
var _triag = CALCULATE( MIN('Table data'[dated]) , FILTER('Table data','Table data'[Serial.no] = EARLIER('Table data'[Serial.no])&& 'Table data'[wc-description] = "TRIAG"))
var _DIAG = CALCULATE( _dia , FILTER('Table data','Table data'[Serial.no] = EARLIER('Table data'[Serial.no])&& 'Table data'[wc-description] = "DIAGNOSIS"))
return 
DATEDIFF(_triag,_DIAG,DAY)

4 Replies

  • AnthonyJoseph's avatar
    AnthonyJoseph
    Resolver III
    3 years ago

    Hi Anonymous 

     

    can you please try the calculated column:

    days diff = 
var _st = 'Table data'[start_date]
var _traig = CALCULATE( _st , FILTER('Table data','Table data'[Serial.no] = EARLIER('Table data'[Serial.no])&& 'Table data'[wc-description] = "TRIAG"))
var _DIAG = CALCULATE( _st , FILTER('Table data','Table data'[Serial.no] = EARLIER('Table data'[Serial.no])&& 'Table data'[wc-description] = "DIAGNOSIS"))
return 
DATEDIFF(_traig,_DIAG,DAY)

     

     

     

    Thanks,

    AnthonyJoseph

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago
       
      days diff =
      var _st = 'PRODUCTIVITY'[DATED]
      var _TRIAG = CALCULATE( _st , FILTER('PRODUCTIVITY','PRODUCTIVITY'[SERIAL_NO] = EARLIER('PRODUCTIVITY'[SERIAL_NO])&& 'PRODUCTIVITY'[WC_DESCRIPTION] = "TRIAG"))
      var _DIAG = CALCULATE( _st , FILTER('PRODUCTIVITY','PRODUCTIVITY'[SERIAL_NO] = EARLIER('PRODUCTIVITY'[SERIAL_NO])&& 'PRODUCTIVITY'[WC_DESCRIPTION]= "DIAGNOSIS"))
      return
      DATEDIFF(_TRIAG,_DIAG,DAY)

       

       

      I modified the formula slightly to the parameters suggested, but it did not work.  In the attached example, it is showing the days elapsed having been 0, when a day has passed between the two actions.

       

      • AnthonyJoseph's avatar
        AnthonyJoseph
        Resolver III
        3 years ago

        Hi Anonymous 

         

        can you try this code:

        days diff = 
var _dia = 'Table data'[dated]
var _triag = CALCULATE( MIN('Table data'[dated]) , FILTER('Table data','Table data'[Serial.no] = EARLIER('Table data'[Serial.no])&& 'Table data'[wc-description] = "TRIAG"))
var _DIAG = CALCULATE( _dia , FILTER('Table data','Table data'[Serial.no] = EARLIER('Table data'[Serial.no])&& 'Table data'[wc-description] = "DIAGNOSIS"))
return 
DATEDIFF(_triag,_DIAG,DAY)