Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
@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.
I would just do this:
DRAFT_TIME =
IF
(
CONTAINS('COMBINED_Articles','COMBINED_Articles'[Status],"Draft"),
([Created Date] - TODAY()) * 1. ,
BLANK()
)
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...
I think that calculation should work in a column.
Maybe I'm misunderstanding the issue, and simply doing something wrong?
It looks for all the world that you are creating a measure. You want to create a column right?
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:
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()
)
That works! Now the LAST problem is that some results are coming up in negatives - any idea why that might be happening?
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()
)
While I certainly appreciate your response and your time, I'm not sure that this has changed too much 🙂
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.
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |