cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
shaunguyver Member
Member

Count number of records filtered by date

I have the following table in my query editor:

 

  • Project Name
  • Project ID
  • Created (Date)
  • Status (Choice of 'Open', 'Closed')

 

I need to create a measure that counts the number of 'Closed' items (under 'Status'), where the 'Created' date is in the last 30 days.

 

How can I express this in DAX?

 

Thanks in advance for any help.

1 ACCEPTED SOLUTION

Accepted Solutions
themistoklis New Contributor
New Contributor

Re: Count number of records filtered by date

@shaunguyver

 

Try the following formula:

Last 30 days =
CALCULATE (
     COUNT( Table[Project ID] ),
              FILTER (
                   ALL ( Table ),
                  Table[Created ]
                   >= MAX ( Table[Created ] ) - 30
                   && Table[Created] <= MAX ( Table[Created] )
                   && Table[Status] = "Closed"
                           )
2 REPLIES 2
themistoklis New Contributor
New Contributor

Re: Count number of records filtered by date

@shaunguyver

 

Try the following formula:

Last 30 days =
CALCULATE (
     COUNT( Table[Project ID] ),
              FILTER (
                   ALL ( Table ),
                  Table[Created ]
                   >= MAX ( Table[Created ] ) - 30
                   && Table[Created] <= MAX ( Table[Created] )
                   && Table[Status] = "Closed"
                           )
shaunguyver Member
Member

Re: Count number of records filtered by date

Thank you!

 

I've tweaked the formula slightly to look for values in the last 30 days as follows:

 

Last 30 days =
CALCULATE (
     COUNT( Table[Project ID] ),
              FILTER (
                   ALL ( Table ),
                  Table[Created ]
                   >= Today() - 30
                   && Table[Created] <= MAX ( Table[Created] )
                   && Table[Status] = "Closed"
                           )