Calculate Days Between Two Rows Based on Conditions from Other Columns

[Anonymous]

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]

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]

@AnthonyJoseph 

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 · Accepted solution]

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]

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