cancel
Showing results for
Did you mean:
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.

 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.

1 ACCEPTED SOLUTION

Accepted Solutions
Established Member

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

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

8 REPLIES 8
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

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

Hi,

What does your base data look like?

Established Member

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

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

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.

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

Member

## 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]
)
)```

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

... And sum it in a measure.

Established Member

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

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

Regards

Affan

Highlighted
Regular Visitor

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

Hi @affan, that worked great, thanks!

Announcements

#### Challenge: Can You Solve These?

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

#### Community News & Announcements

Get your latest community news and announcements.

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

#### Win Power BI Swag with Community Kudopalooza!

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

Top Kudoed Authors
Users Online
Currently online: 146 members 1,631 guests
Recent signins: