cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
andrew_hardwick Regular Visitor
Regular Visitor

How to calculate Tickets Opened and resolved in the same day

I have a table showing the number of tickets opened on a given date and also resolved on that same date. What I am trying to find now is the number where the opened date and the resolved date are the same. The output should look similar to below.

 

 OpenedResolved Same Day
1/9/201854
2/9/20184439
3/9/2018260127
4/9/201821476
5/9/201813753
6/9/201813849
7/9/20189947
8/9/201822
9/9/201833

 

I have tried DAX like below

 

ResolvedSameDay = CALCULATE([Opened],Incident[OpenDate]=Incident[ResolvedDate])

 

But it finds the OpenDate field but will not accept the ResolvedDate field even though it does exist.

1 ACCEPTED SOLUTION

Accepted Solutions
affan Established Member
Established Member

Re: How to calculate Tickets Opened and resolved in the same day

Hi @andrew_hardwick

 

You can use the follwoing measure 

 

ResolvedSameDay = 
CALCULATE(COUNTA(Incident[OpenDate]),FILTER(Incident,CALCULATE(MAX(Incident[OpenDate]),ALLEXCEPT(Incident,Incident[OpenDate]))=Incident[ResolvedDate]))

 

I have tested the same and I got the below result

Resolved same day1.png

 

Resolved same day.png

 

 

 

If this helped you, please mark this post as an accepted solution and like to give KUDOS .

 

Regards,

Affan

 

8 REPLIES 8
HotChilli New Contributor
New Contributor

Re: How to calculate Tickets Opened and resolved in the same day

Something like

SUMMARIZECOLUMNS(
        Incident[OpenDate],
        "Calls Opened", COUNT(Incident[OpenDate]),
        "Call Resolved Same Day", COUNTX(
        				FILTER(Incident, Incident[ResolvedDate] = Incident[OpenDate]), Incident[OpenDate])
        )
Super User
Super User

Re: How to calculate Tickets Opened and resolved in the same day

Hi,

 

What does your base data look like?

affan Established Member
Established Member

Re: How to calculate Tickets Opened and resolved in the same day

Hi @andrew_hardwick

 

You can use the follwoing measure 

 

ResolvedSameDay = 
CALCULATE(COUNTA(Incident[OpenDate]),FILTER(Incident,CALCULATE(MAX(Incident[OpenDate]),ALLEXCEPT(Incident,Incident[OpenDate]))=Incident[ResolvedDate]))

 

I have tested the same and I got the below result

Resolved same day1.png

 

Resolved same day.png

 

 

 

If this helped you, please mark this post as an accepted solution and like to give KUDOS .

 

Regards,

Affan

 

andrew_hardwick Regular Visitor
Regular Visitor

Re: How to calculate Tickets Opened and resolved in the same day

Hi @HotChilli I tried to create this as a measure and a column, but I got the error The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

HotChilli New Contributor
New Contributor

Re: How to calculate Tickets Opened and resolved in the same day

The DAX i've written creates a new table

 

Go to Modelling->New Table

Re: How to calculate Tickets Opened and resolved in the same day

@HotChilliprovided a complete query.

 

The expression you want to add as measure is the following:

Incident resolved on the same day = COUNTX(
    FILTER(Incident, Incident[ResolvedDate] = Incident[OpenDate])
            , Incident   [OpenDate]
    )
)

Alternatively, you could also add a new column to your model:

Same day resolution = IF(Incident[ResolvedDate] = Incident[OpenDate]; 1)

... And sum it in a measure.

Highlighted
affan Established Member
Established Member

Re: How to calculate Tickets Opened and resolved in the same day

Hi @andrew_hardwick,

 

If your problem is resolved with my solution, please mark it as accepted so the thread is closed.

 

Regards

Affan

andrew_hardwick Regular Visitor
Regular Visitor

Re: How to calculate Tickets Opened and resolved in the same day

Hi @affan, that worked great, thanks!

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 336 members 3,433 guests
Please welcome our newest community members: