Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Calculated Column: DATEDIFF with filter - not working + getting negative numbers

Hi folks,

I'm hoping someone can help me. I'm trying to create a calculated column to show the time elapsed, in days, between when a piece of documentation was created [Created Date], and TODAY() - but only if the [Status] of the documentation is "Draft". If the [Status]<> "Draft", the calculation should return a blank. My column formula and some sample data is below. I have two challenges: My calculation below is returning results regardless of the [Status], and I'm also getting some (not all) negative numbers which I don't understand, looking at the [Created Date] field:

 

 

 

 

DRAFT_TIME = IF
(
CONTAINS('COMBINED_Articles','COMBINED_Articles'[Status],"Draft"),
DATEDIFF
(
[Created Date],TODAY(), DAY) 
)

 

 

 

 

 

 

Any help would really be appreciated!

Need Help

13 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    I would just do this:

     

    DRAFT_TIME = 
  IF
  (
    CONTAINS('COMBINED_Articles','COMBINED_Articles'[Status],"Draft"),
    ([Created Date] - TODAY()) * 1. ,
    BLANK()
  )
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks so much for the quick feedback Greg_Deckler  - after your reply I realied that what I'm actually trying to create is a calculated column, not a measure - I do apologise. I've editied the original post accordingly...

  • smpa01's avatar
    smpa01
    Community Champion
    6 years ago

     

    Anonymous CONTAINS is not doing the job you thought it does. CONTAINS check if at least 1 row of the table is meeting the condition. CONTAINSTRING is your guy.