Counting days between date and today's date using DATEDIFF only if criteria is met

[Oranjeboomer]

I've just started using Power BI so still getting my head around everything. Googling keeps me bringing back to this useful community so here I am with my first question.

I need to count days from a date column using today's date and that's something I have achieved using DateDiff:

Open Count Days = DATEDIFF('Table 1'[Query Open Date],TODAY(),DAY)

However I only want to count days if a certain criteria is met using data from another column ("Query status").

So Open Count Days should only count days for those values of 'Answered' and 'Open' in Query Status values. So where the values are 'Cancelled' I do not want the count to perform:

How can I best amend the above?

Thanks in advance.

[gmsamborn · Accepted solution]

Hi @Oranjeboomer 

You could change your calculated column to something like this.

Open Count Days = 
    IF(
        NOT [Query status] IN { "Closed", "Cancelled" },
        DATEDIFF(
            'Sheet1'[Ticket open date],
            TODAY(),
            DAY
        )
    )

This won't show "NA" for Closed or Cancelled.  I tried to force it but a calculated column can't have a datatype of variant.  It would be posssible to convert the results of DATEDIFF to text.  The only problem is that since they are text values, they won't add up.

My preference would be the above DAX that just show blank for Closed or Cancelled.  That way, it is a number instead of text.

[Oranjeboomer · Accepted solution]

Thanks very much! That works well.

[gmsamborn]

I would like to help but I'm a little unclear about your requirements.

Can you provide the following?

1)  Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

2) Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

3) Please explain how you would expect to get from step 1 to 2.

4) If possible, please show your past attempts at a solution.

A .pbix file with sample data would be best.

[Oranjeboomer]

Please find link to the pbix.

My desired outcome:

Ticket open date	Query status	Open Count days
12/02/2024	Closed	NA
13/02/2024	Open	28
01/02/2024	Answered	40
15/02/2024	Cancelled	NA
14/02/2024	Open	27
17/02/2024	Open	24

Like I said, I've used:     Open Count Days = DATEDIFF('Sheet1'[Ticket open date],TODAY(),DAY)

but that needs to be tweaked to build in additional conditions that days are only counted for Open and Answered statuses. All others can have a return status of 'NA'

[gmsamborn · Accepted solution]

Hi @Oranjeboomer 

You could change your calculated column to something like this.

Open Count Days = 
    IF(
        NOT [Query status] IN { "Closed", "Cancelled" },
        DATEDIFF(
            'Sheet1'[Ticket open date],
            TODAY(),
            DAY
        )
    )

This won't show "NA" for Closed or Cancelled.  I tried to force it but a calculated column can't have a datatype of variant.  It would be posssible to convert the results of DATEDIFF to text.  The only problem is that since they are text values, they won't add up.

My preference would be the above DAX that just show blank for Closed or Cancelled.  That way, it is a number instead of text.