Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Oranjeboomer
New Member

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

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").

 

Oranjeboomer_0-1710273818736.png

 

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:
 
Oranjeboomer_2-1710274484939.png

 

 
How can I best amend the above?

 

Thanks in advance.

 

2 ACCEPTED SOLUTIONS

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.



Proud to be a Super User!

daxformatter.com makes life EASIER!

View solution in original post

Oranjeboomer
New Member

Thanks very much! That works well.

View solution in original post

4 REPLIES 4
Oranjeboomer
New Member

Thanks very much! That works well.

gmsamborn
Super User
Super User

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.



Proud to be a Super User!

daxformatter.com makes life EASIER!

Please find link to the pbix.

 

My desired outcome:

 

Ticket open dateQuery statusOpen Count days
12/02/2024ClosedNA
13/02/2024Open28
01/02/2024Answered40
15/02/2024CancelledNA
14/02/2024Open27
17/02/2024Open24

 

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'

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.



Proud to be a Super User!

daxformatter.com makes life EASIER!

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors