topic Counting dates in DAX Commands and Tips

Counting dates

Anonymous — Tue, 29 Oct 2024 08:47:22 GMT

Hi,

I have a table looking a items and for each there is a Date Opened and a Date Closed. I am trying to create a table to show, by team member, how many items they had opened and closed each day in the last 30 days.

I've created a table and used Relative Date filter for last 30 days, but for some reason my counts of Open and Closed are showing the same number.

At the moment I am dragging the Opened and Closed date fields into the table and counting. Do I need to use a measure instead and if so what would people suggest?

Re: Counting dates

shafiz_p — Tue, 29 Oct 2024 09:08:01 GMT

Hi Anonymous Share example data and the expected result you want.

Re: Counting dates

Anonymous — Tue, 29 Oct 2024 09:27:40 GMT

Sample Data:

How I am trying to summarise:

I am at the moment bringing in the three fields (Manager, Date Opened, Date Closed) into a table and using a Relative Date filter to capture last 30 days.

Re: Counting dates

Anonymous — Wed, 30 Oct 2024 02:33:36 GMT

Hi Anonymous

Pls try this:

Create measures.

Opened Count = var _opendcount = CALCULATE( COUNTROWS('Table'), FILTER( 'Table', 'Table'[Date Opened] >= UTCNOW() - 30 && 'Table'[Date Opened] <= UTCNOW() ) ) RETURN IF( ISBLANK(_opendcount), 0, _opendcount )

Closed Count = var _closedcount = CALCULATE( COUNTROWS('Table'), FILTER( 'Table', 'Table'[Date Closed] >= UTCNOW() - 30 && 'Table'[Date Closed] <= UTCNOW() ) ) RETURN IF( ISBLANK(_closedcount), 0, _closedcount )

Where you can use UTCNOW () to get relative dates.

Relative Date = UTCNOW()

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.