Custom columns with timestamps and date transformations

[RohithGn]

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

[DanielV91]

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

[MarcelBeug · Accepted 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.

[RohithGn]

Thanks a ton @MarcelBeug your code is working as expected

 @DanielV91 Thanks a lot for the reply 🙂

[DanielV91]

@MarcelBeug Thanks for noticing, I should have paid more attention, my bad.