Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi All,
I am working on a report in which we have 3 columns containing timestamps - Opened, Resolved & Closed. Our concern is with the null values in Resolved column. I am trying to create a custom column where If Resolved is null and Closed is null then Resolved row should contain the same value as Opened and If Resolved is null and Closed is having a value then resolved should contain closed date - 7days
Eg -
Opened | Resolved | Closed | Expected column |
2017-03-29 08:44:15 | 2017-04-04 08:44:15 | Resolved | |
2/29/2017 8:41:43 AM | null | 2017-03-15 08:41:43 | closed -7 days |
2017-03-29 08:33:18 | null | null | opened |
First, I have created a custom column - resolved -1 where I used the formula as follows - "resolved-1", each if [Resolved] = null and [Closed]=null then [Opened]else [Resolved])
Then I tried to create another custom column from Resolved 1 with formula - ("Final resolve", each if [#"resolved-1"] =null then [Closed] - 7 else [#"resolved-1"])
Now I am getting an error for all the null values in the Final resolve column I am getting this error -
Expression.Error: We cannot apply operator - to types DateTime and Number.
Details:
Operator=-
Left=4/5/2017 9:47:46 AM
Right=7
Any response is highly appreciated.
Regards,
Rohith
Solved! Go to Solution.
@DanielV91 there are some superfluous checks in your code: if a condition is not true, then you don't need to check in the else part if the condition is false.
Assuming the data is correct, your code can be shortened to:
if [Resolved] <> null then [Resolved] else if [Closed] = null then [Opened] else Date.AddDays([Closed],-7)
Note: Date.AddDays can also be used with datetime type data, so no need to convert to date.
Hello,
First of all, 2017 is not a leap year : so 29 of February doesn't exist.
Second, make sure to properly format that date type; I suggest you only keep the date and get rid of the hours.
I managed to achive your requirement with the following query:
if [Resolved] <> null then [Resolved] else if [Resolved] = null and [Closed] = null then [Opened] else if [Closed] <> null then Date.AddDays([Closed],-7) else "Check Data!"
See the result in the following picture and let me know if this helped you
@DanielV91 there are some superfluous checks in your code: if a condition is not true, then you don't need to check in the else part if the condition is false.
Assuming the data is correct, your code can be shortened to:
if [Resolved] <> null then [Resolved] else if [Closed] = null then [Opened] else Date.AddDays([Closed],-7)
Note: Date.AddDays can also be used with datetime type data, so no need to convert to date.
Thanks a ton @MarcelBeug your code is working as expected
@DanielV91 Thanks a lot for the reply 🙂
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
144 | |
73 | |
64 | |
52 | |
51 |
User | Count |
---|---|
208 | |
91 | |
62 | |
59 | |
56 |