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)
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