COUNTROWS using date problem

[Dnssdrs]

I’m trying to create a weekly trend that tracks my job’s issue tickets — specifically the ‘Created Ticket’ and ‘Closed Ticket’ for that week.
I’ve successfully created a measure for the ‘Created Ticket’, but I’m unable to use the same formula to create the ‘Closed Ticket’ measure.

For background, I have two tables: the First is my calendar table, and the Second contains issue titles, details, created dates, and closed dates for the tickets.
I’ve created a relationship (One-to-Many) from the first table’s ‘Date’ column to the second table’s ‘Created Ticket’ column.

Here’s what my trend looks like right now:

The ‘Closed Issue’ trend is displayed incorrectly; it should show a higher value and on the most X-axis.

Does anyone have any suggestions?

[BeaBF · Accepted solution]

@Dnssdrs Hi!

Which are your measures? Have you tried with:

Closed Tickets =
CALCULATE(
COUNTROWS(Tickets),
USERELATIONSHIP(Calendar[Date], Tickets[Closed Date])
)

You can also try with:

Closed Tickets =
COUNTROWS(
FILTER(
Tickets,
Tickets[Closed Date] >= MIN(Calendar[Date]) &&
Tickets[Closed Date] <= MAX(Calendar[Date])
)
)

BBF

💡 Did I answer your question? Mark my post as a solution!

👍 Kudos are appreciated

🔥 Proud to be a Super User!

[Kedar_Pande · Accepted solution]

Your relationship is wrong. You linked Calendar to Created Date, but Closed Date needs its own relationship.

Create an inactive relationship from Calendar[Date] to your tickets table[Closed Date]. Then use this measure:

Closed Tickets =
CALCULATE(
COUNTROWS(Tickets),
USERELATIONSHIP(Calendar[Date], Tickets[Closed Date])
)

Use this measure in your trend line.

If this answer helped, please click Kudos or mark as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pande

[BeaBF · Accepted solution]

@Dnssdrs Hi!

Which are your measures? Have you tried with:

Closed Tickets =
CALCULATE(
COUNTROWS(Tickets),
USERELATIONSHIP(Calendar[Date], Tickets[Closed Date])
)

You can also try with:

Closed Tickets =
COUNTROWS(
FILTER(
Tickets,
Tickets[Closed Date] >= MIN(Calendar[Date]) &&
Tickets[Closed Date] <= MAX(Calendar[Date])
)
)

BBF

💡 Did I answer your question? Mark my post as a solution!

👍 Kudos are appreciated

🔥 Proud to be a Super User!