How to count of tickets closed (or not closed) in same time period as created

[alagator28]

Hello,

I have a Tickets table with an ID, CreatedDate, ClosedDate, etc. I also have a Dates table linked to the Tickets table.

How could I get the count of tickets which were Closed within the same time period as they were created? Here's an example of what I would want to know:

• 50 tickets created in January
• Of those 50 tickets, how many were also closed in January? And how many were not?
• For this measure, I only want to look at tickets in the same time period, not tickets from a previous time period.

Thanks in advance.

[v-kongfanf-msft · Accepted solution]

Hi @speedramps ,

Try formula like below:

M_created =
COUNTROWS (
    FILTER (
        ALL ( Ticket ),
        Ticket[Created].[Month] = MAX ( Ticket[Created].[Month] )
    )
)

M_closed =
COUNTROWS (
    FILTER (
        ALL ( Ticket ),
        Ticket[Created].[Month] = MAX ( Ticket[Created].[Month] )
            && Ticket[Created].[Month] = Ticket[End].[Month]
    )
)

M_notclosed =
COUNTROWS (
    FILTER (
        ALL ( Ticket ),
        Ticket[Created].[Month] = MAX ( Ticket[Created].[Month] )
            && ISBLANK ( Ticket[End] )
    )
)

Best Regards,
Adamk Kong

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

[speedramps · Accepted solution]

@alagator28 

Try this solution

Click to download PBIX from OneDrive 

How it works ...

See my earlier post about how to create the fisrt half of the solution.

The use this measure which build a temp table of created tickets,  a temp table of created tickets 

and then interects them to find just the tickets on both.

Closed same month = 
VAR created = CALCULATETABLE(VALUES(Tickets),
    USERELATIONSHIP('Calendar'[Date],Tickets[Created date]))

VAR closed = CALCULATETABLE(VALUES(Tickets),
    USERELATIONSHIP('Calendar'[Date],Tickets[End Date]))

VAR both = INTERSECT(created,closed)

RETURN
COUNTROWS(both)

This solution does exactly what you asked for.

Please be polite and show your thanks by promptly clicking the "accept a solution" and thumbs up button.

Remember we are unpaid volunteers and you may need expert help again.

One question per ticket please. If you have any more questions then raise a new ticker and quote @speedramps and I will be happy to try answer them.

You will get quicker and better answers by splitting complex problems into smaller problems and each helper will get the kudos they deserve.

[alagator28]

Thank you, @speedramps . 

Sorry for the delay, I was off work for a while. Looks like this would work, although this is the route I took:

ClosedInMonth = IF(Tickets[TodayMonthName] = Tickets[CreationMonthName],

                        IF(Tickets[ClosedMonthName] = Tickets[CreationMonthName], "Yes", BLANK()),

                        If(Tickets[CreationMonthName] <> Tickets[ClosedMonthName], "No", "Yes"))

Then used two measures that I can display in a visual graph:

#TicketsClosedInMonth = CALCULATE(Tickets[#Tickets], FILTER(Tickets, Tickets[ClosedInMonth] = "Yes"))

#TicketsNotClosedInMonth = CALCULATE(Tickets[#Tickets], FILTER(Tickets, Tickets[ClosedInMonth] = "No"))

[v-kongfanf-msft · Accepted solution]

Hi @speedramps ,

Try formula like below:

M_created =
COUNTROWS (
    FILTER (
        ALL ( Ticket ),
        Ticket[Created].[Month] = MAX ( Ticket[Created].[Month] )
    )
)

M_closed =
COUNTROWS (
    FILTER (
        ALL ( Ticket ),
        Ticket[Created].[Month] = MAX ( Ticket[Created].[Month] )
            && Ticket[Created].[Month] = Ticket[End].[Month]
    )
)

M_notclosed =
COUNTROWS (
    FILTER (
        ALL ( Ticket ),
        Ticket[Created].[Month] = MAX ( Ticket[Created].[Month] )
            && ISBLANK ( Ticket[End] )
    )
)

Best Regards,
Adamk Kong

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

[alagator28]

Thanks @v-kongfanf-msft.

Sorry for the delay, I was off work for a while. Looks like this would work, although this is the route I took:

ClosedInMonth = IF(Tickets[TodayMonthName] = Tickets[CreationMonthName],

                        IF(Tickets[ClosedMonthName] = Tickets[CreationMonthName], "Yes", BLANK()),

                        If(Tickets[CreationMonthName] <> Tickets[ClosedMonthName], "No", "Yes"))

Then used two measures that I can display in a visual graph:

#TicketsClosedInMonth = CALCULATE(Tickets[#Tickets], FILTER(Tickets, Tickets[ClosedInMonth] = "Yes"))

#TicketsNotClosedInMonth = CALCULATE(Tickets[#Tickets], FILTER(Tickets, Tickets[ClosedInMonth] = "No"))

[speedramps]

Download this solution ...
Click here to download a PBIX from OneDrive 

How it works ...
Create 2 inactive relationships (for create date and end date)

Create meaures

 Created = CALCULATE( COUNTROWS(Tickets), USERELATIONSHIP('Calendar'[Date],Tickets[Created date]) )Closed = CALCULATE( COUNTROWS(Tickets), USERELATIONSHIP('Calendar'[Date],Tickets[End Date] )) Please can you be a bit more specific (with examples) what you want in the 3rd column.

I prefer just one question per post.

Remember we are unpaid volunteers, and you have got free expert help which took a lot of effort,

Please accept this solution and open a  new case for the other problem.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

If you quote @speedramps in your next tickets then I will then receive an automatic notification, and will be delighted to help you again.

Please now click the [accept as solution] and the thumbs up button.  Thank you.

[alagator28]

Thank you @speedramps . I didn't realize I had asked more than one question, so I apologize if that's how it came out. I looked through your PBIX and it's not giving the result I need. I'll explain using your data:

If we look at August, 2021, there were 7 tickets created during the month, but somehow more tickets were closed than were created. As mentioned, I only want to count the closed tickets from the created ones in the month.

When looking at the tickets, we can see that there were in fact 7 tickets created in August, 2021. That's good. However, of those 7 created in August, 2021, only 2 were also closed in August, 2021.

The result I'm looking for in August 2021 would be Created = 7, Closed =2.

[amustafa]

Question...in your Tickets table do you repeat the ID to capture ClosedDate for same ID or do you update the same row with a ClodeDate?

Ideally, you would update the row for same ID. So the Tickts tabe wild have a unique row for an ID. You can then just create a isClosed flag (0=Closed, 1=NotClosed) value if ClosedDate is null then you can simply sum the isClosed as NotClosed.

[alagator28]

@amustafa , yes, I have all ticket information in one row. I clarified my question in the response above in case it helps. Thanks.