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.

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

Highlighted
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
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 146 members 1,631 guests
Please welcome our newest community members: