Subtract Today() from a Column

[Anonymous]

Hello 

I am using a formula in excel (=IF(AY2<>"NA",TODAY()-BB2,"OPEN")). 

I want to subtract Column "BB" using Today() function if value in column "AY" is not equal to "NA" in form of number in "BC" column else it should show open.

Please help @v-xiaotang 

[parry2k · Accepted solution]

@Anonymous sorry missed a parenthesis

New Column Name = IF(Table[AY2 Column Name] <> "NA", 
FORMAT ( DATEDIFF ( TODAY(), Table[BB2 Column Name], DAY ), "General Number" ),
"OPEN")
). 

✨ Follow us on LinkedIn and  to our YouTube channel

Check my latest video on Filters and Sparklines https://youtu.be/wmwcX8HvNxc

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[parry2k · Accepted solution]

@Anonymous sorry, just reverse the column in the datediff

 DATEDIFF ( Table[BB2 Column Name], TODAY(),  DAY )

✨ Follow us on LinkedIn and  to our YouTube channel

Check my latest video on Filters and Sparklines https://youtu.be/wmwcX8HvNxc

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[parry2k]

@Anonymous what is the type of AY2 column in Power BI, is it a text or a number?

✨ Follow us on LinkedIn and  to our YouTube channel

Check my latest video on Filters and Sparklines https://youtu.be/wmwcX8HvNxc

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[Anonymous]

It worked man !! Thanks . Just one thing the data is coming in negative. how can we correct that. Thanks once again.

[Anonymous]

[parry2k · Accepted solution]

@Anonymous sorry missed a parenthesis

New Column Name = IF(Table[AY2 Column Name] <> "NA", 
FORMAT ( DATEDIFF ( TODAY(), Table[BB2 Column Name], DAY ), "General Number" ),
"OPEN")
). 

✨ Follow us on LinkedIn and  to our YouTube channel

Check my latest video on Filters and Sparklines https://youtu.be/wmwcX8HvNxc

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[Anonymous]

Still error 😔

DAX comparison operations do not support comparing values of type Integer with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

[mh2587]

=DATEDIFF( [Closed On], Today(), DAY )

[Anonymous]

Getting this error - Cannot convert value 'OPEN' of type Text to type Date.

[parry2k]

@Anonymous add this column

New Column Name = IF(Table[AY2 Column Name] <> "NA", 
FORMAT ( DATEDIFF ( TODAY(), Table[BB2 Column Name], DAY ), "General Number",
"OPEN")
). 

✨ Follow us on LinkedIn and  to our YouTube channel

Check my latest video on Filters and Sparklines https://youtu.be/wmwcX8HvNxc

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[Anonymous]

Getting this error - "DAX comparison operations do not support comparing values of type Integer with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values."