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 PBI25 Carousel

Power BI Monthly Update - July 2025

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

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

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