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

[Anonymous]

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!

[smpa01]

@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.

[Greg_Deckler]

I would just do this:

DRAFT_TIME = 
  IF
  (
    CONTAINS('COMBINED_Articles','COMBINED_Articles'[Status],"Draft"),
    ([Created Date] - TODAY()) * 1. ,
    BLANK()
  )

[Anonymous]

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

[Greg_Deckler]

I think that calculation should work in a column.

[Anonymous]

Maybe I'm misunderstanding the issue, and simply doing something wrong?

[Greg_Deckler]

It looks for all the world that you are creating a measure. You want to create a column right?

[Anonymous]

Apologies again, I had gotten mixed up (that'll teach me for rushing) and was indeed trying to create a measure. I've sucessfully used your code to create a calculated column (thanks again), but 1) I'm getting negative numbers, and 2) it's not returning blank results for the non-'draft' articles:

[Greg_Deckler]

Right, easy to fix. I was using your code for the IF true/false statement, I would do it this way:

DRAFT_TIME = 
  IF
  (
    COMBINED_Articles'[Status] <> "Draft",
    (TODAY() - [Created Date]) * 1. ,
    BLANK()
  )

[Anonymous]

That works! Now the LAST problem is that some results are coming up in negatives - any idea why that might be happening?

[Greg_Deckler]

So, that's fixable. Couple different ways of doing it but one method is:

DRAFT_TIME = 
  VAR __Table = { [Created Date], TODAY() }
  VAR __DateMax = MAXX(__Table,[Value])
  VAR __DateMin = MINX(__Table,[Value])
RETURN
  IF
  (
    COMBINED_Articles'[Status] <> "Draft",
    (__DateMax - __DateMin) * 1. ,
    BLANK()
  )

[Anonymous]

While I certainly appreciate your response and your time, I'm not sure that this has changed too much 🙂

[Greg_Deckler]

Change your data type on your Created Date column to be Date Time column instead of text. If it was a Date or Date Time, it would be in italics in your screen shot.

[smpa01]

Because on the negative value rows the created date > today.