Counting dates

[WC32]

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?

[Anonymous · Accepted solution]

Hi @WC32 

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.

[WC32]

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.

[shafiz_p]

Hi @WC32  Share example data and the expected result you want.