Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I want to create a new column based on the condition that hortlistcount is 0 and Duedate is less than two days from now or overdue
If so, it should say 1 else 0.
So I made this:
= Table.AddColumn(#"Changed Type1", "red", each if [DueDate] = Date.AddDays (Date.From(DateTime.FixedLocalNow()), -2) and [ShortListCount] = 0 then "1" else 0)
And it gives me 0 every time.
Any idea what I'm doing wrong?
Solved! Go to Solution.
You have to ask first for null in order to avoid the comparison between null and dates.
if [DueDate] = null then 0 else if [DueDate] <= Date.AddDays (DateTime.Date(DateTime.LocalNow()), -2) and [ShortListCount] = 0 then 1 else 0
Be carefull with quotes. If you type "1" the result will be a text 1 and not a number.
Regards,
Happy to help!
Hi. In the definition you mention a duedate is less than two days from now and the formula is duedate is exactly two days ago.
Are you sure you are not looking for this:
= Table.AddColumn(#"Changed Type1", "red", each if [DueDate] <= Date.AddDays (Date.From(DateTime.FixedLocalNow()), -2) and [ShortListCount] = 0 then "1" else 0)
Be sure to show us a row that is not fulfilling this conditions in case you have 0 again.
Regards,
Happy to help!
Hi @ibarrau and thanks for answering.
I tried that as well but then I get an error in all columns:
This should trigger 1
Ok, let's use a Date for now instead Datetime.
= Table.AddColumn(#"Changed Type1", "red", each if [DueDate] <= Date.AddDays (DateTime.Date(DateTime.LocalNow()), -2) and [ShortListCount] = 0 then "1" else 0)
This should work.
Regards,
Happy to help!
Still the same error, but I get 0 when the duedate is null
seems to me that id does not like the < sign when using dates?
Can you please show us the error text? because I just test it and I'm sure it is not a date problem this time.
Happy to help!
@ibarrauah, I managed to get it to work. I messed up the "applied steps" a bit and it broke cause of that.
So now I get both 0 and 1, but it still says that there are errors?
Ok, it shows error when duedate is null. That's logic 🙂 But is it a way to handle that? show 0 if duedate is null
Hi, sure you can add and if [duedate] = null then ......... else [start the other condition]
You can decide if you want a value for this cases like 0 or 1 and add it there.
Regards,
Happy to help!
I'm still getting errors on that one.
I have this one now:
if [DueDate] <= Date.AddDays (DateTime.Date(DateTime.LocalNow()), -2) and [ShortListCount] = 0 then "1" else if [DueDate] = null then 0 else 0
get this error:
You have to ask first for null in order to avoid the comparison between null and dates.
if [DueDate] = null then 0 else if [DueDate] <= Date.AddDays (DateTime.Date(DateTime.LocalNow()), -2) and [ShortListCount] = 0 then 1 else 0
Be carefull with quotes. If you type "1" the result will be a text 1 and not a number.
Regards,
Happy to help!
hi @ibarrau .
Seems like the formula is a bit buggy. I wanted it to set the value to 1 if the duedate is three days from now or less and shortlist is 0. I now have a case where duedate is tomorrow and shortlist is 0, and the value is still 0.
Do you see any obvius reasons for it not to be 1?
User | Count |
---|---|
93 | |
83 | |
77 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |