Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
WC32
New Member

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?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

 

 

vnuocmsft_0-1730255465099.png

 

Here is the result.

 

vnuocmsft_1-1730255508879.png

 

Regards,

Nono Chen

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

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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

 

 

vnuocmsft_0-1730255465099.png

 

Here is the result.

 

vnuocmsft_1-1730255508879.png

 

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
New Member

Sample Data:

WC32_0-1730194005370.png


How I am trying to summarise:

WC32_1-1730194051702.png


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
Super User
Super User

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.