Count number of records filtered by date

[shaunguyver]

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.

[themistoklis · Accepted solution]

@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]

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