Forum Discussion

lguelcher's avatar
lguelcher
Regular Visitor
10 years ago
Solved

If won, then calculate difference between two dates

I'm trying to add a calculated column to calculate the number of days between two dates. However, I need to only calculate for those that meet a certain criteria.    The formula I tried is:  DiffC...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    10 years ago

    Are you sure that you are using a calculated column because the error you are getting is for a measure. If you use a calculated column, your formula would generate an error regarding "variant" data because you are trying to return a number and a string at the same time. Both have to agree in terms of the data type returned. So, you could do something like this as a calculated column (not a measure)

     

    DiffCloseDate = IF(Opportunities[Status]="won",Opportunities[Actual Close Date]-Opportunities[Est Close Date],-999)
Greg_Deckler's avatar
Greg_Deckler
Community Champion
10 years ago

Are you sure that you are using a calculated column because the error you are getting is for a measure. If you use a calculated column, your formula would generate an error regarding "variant" data because you are trying to return a number and a string at the same time. Both have to agree in terms of the data type returned. So, you could do something like this as a calculated column (not a measure)

 

DiffCloseDate = IF(Opportunities[Status]="won",Opportunities[Actual Close Date]-Opportunities[Est Close Date],-999)
greggyb's avatar
greggyb
Resident Rockstar
10 years ago

lguelcher, are you defining this column in the 'Opportunities' table?