How to calculate Tickets Opened and resolved in the same day

[andrew_hardwick]

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.

	Opened	Resolved Same Day
1/9/2018	5	4
2/9/2018	44	39
3/9/2018	260	127
4/9/2018	214	76
5/9/2018	137	53
6/9/2018	138	49
7/9/2018	99	47
8/9/2018	2	2
9/9/2018	3	3

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.

[affan · Accepted solution]

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

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

Regards,

Affan

[andrew_hardwick]

Hi @affan, that worked great, thanks!

[affan]

Hi @andrew_hardwick,

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

Regards

Affan

[Ashish_Mathur]

Hi,

What does your base data look like?

[HotChilli]

Something like

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

[andrew_hardwick]

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.

[LaurentCouartou]

@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.

[HotChilli]

The DAX i've written creates a new table

Go to Modelling->New Table