Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Counting dates

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's avatar
    Anonymous
    1 year ago

    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.

3 Replies

  • shafiz_p's avatar
    shafiz_p
    Super User
    1 year ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.