Reply
Regular Visitor
Posts: 33
Registered: ‎05-13-2016
Accepted Solution

How to calculate Tickets Opened and resolved in the same day

[ Edited ]

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.


Accepted Solutions
Established Member
Posts: 201
Registered: ‎07-12-2017

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

 

View solution in original post


All Replies
Member
Posts: 116
Registered: ‎09-10-2018

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
Posts: 3,943
Registered: ‎01-14-2017

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

Hi,

 

What does your base data look like?

Established Member
Posts: 201
Registered: ‎07-12-2017

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

 

Regular Visitor
Posts: 33
Registered: ‎05-13-2016

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.

Member
Posts: 116
Registered: ‎09-10-2018

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

Member
Posts: 128
Registered: ‎09-29-2016

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
Established Member
Posts: 201
Registered: ‎07-12-2017

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

Regular Visitor
Posts: 33
Registered: ‎05-13-2016

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

Hi @affan, that worked great, thanks!