Calculate the days between a date and today only based on the value of another field

[stokidez]

Hope someone can point me in the right direction please.

I want the field "Days Open" to display the difference between Today's date and Date Identified (GMT) in days. However, I only want it to populate if the Status field is = "Open".

This is what I have so far (which PowerBI doesn't like).

Days Open = IF('Breach Log'[Status] = "Open",DATEDIFF('Breach Log'[Date Identified (GMT)],TODAY(),DAY),"")

Error: Expressions that yield variant data-type cannot be used to define calculated columns.

Any help would be much appreciated!

[edugoncalves · Accepted solution]

Hi @stokidez ,

Does your table have null values on 'Breach Log'[Date Identified (GMT)?

If not, try to return BLANK() instead of "" in your if-statement.

DATEDIFF returns a numeric value and you're forcing a string if the statement is false.

Best Regards,

Eduardo

[edugoncalves · Accepted solution]

Hi @stokidez ,

Does your table have null values on 'Breach Log'[Date Identified (GMT)?

If not, try to return BLANK() instead of "" in your if-statement.

DATEDIFF returns a numeric value and you're forcing a string if the statement is false.

Best Regards,

Eduardo

[stokidez]

Thank you @edugoncalves ! That has worked 😀

[stokidez]

I've added this field now to a table which shows all breaches regardless of status and it is automatically only displaying "Open" breaches and no longer displaying "potential" or "closed". Is there a way to force the table to show all breaches regardless of status? @edugoncalves Thanks,

[edugoncalves · Accepted solution]

Hi @stokidez ,
I am happy to know that I have given a positive contribution to resolving this issue.
One of the disadvantages of using BLANK() is when we use the column in a visual, it will, by default, ignore them.

There are two solutions to this:

1st - Show items without data in the visual

2nd - Instead of using blank (), use, for example, the value -1.

Choose the one that makes the most sense for your report. ‌‌

Best Regards,
Eduardo

[stokidez]

@edugoncalves You star! 1st option was perfect for what I needed. Learnt two new things now thanks to you - really appreciate the support.

[edugoncalves]

@stokidez 
I am truly happy to know that you have learned something new today, continue to explore and you will learn more and more every day! ‌‌😋

Best Regards,

Eduardo