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)
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)
  • AnthonyJoseph's avatar
    AnthonyJoseph
    Resolver III
    3 years ago

    Hi Anonymous  screenshot below:

     

    if this output is incorrect, can you share the expected output in table format so me to help better...

     

    Thanks,

    AnthonyJoseph