Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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?
Solved! Go to 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.
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.
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.
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
26 | |
10 | |
10 | |
9 | |
6 |