cancel
Showing results for
Did you mean:
Frequent Visitor

## Calculating days active cases

Hi,

I'm kinda new to DAX and I'm experiencing some weird behaviour on a calculation
I'm trying to count how many days a given ticket number (customer case) is active until today.

Here is the result:

The ticket number (CAS-24075 where the CRM unit is owner of) is open for 641 days, but for some reason he is calculating something for the other business units.
I would expect that for that ticket only 1 row gets returned with the result of 641 days.

Here is my DAX formula:

I would expect a result as follows where the not owning business units have blank results:

What am I doing wrong?

Regards

K.

1 ACCEPTED SOLUTION
Community Champion

You can try something like below.

```Oldest Active Ticket =
VAR _active =
CALCULATETABLE(
SUMMARIZE(
Incident,
Incident[Ticket Number],
Incident[Unit Name]
),
"start",
CALCULATE(
COUNTROWS( dates ),
DATESBETWEEN( dates[Date], MIN( Incident[CreatedOn_DateKey] ), TODAY() ),
dates[isWeekend] = FALSE()
)
),
KEEPFILTERS( 'Incident'[State Code] = "Active" )
)
RETURN
SUMX( _active, [start] )
```
Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

Community Champion

You can try something like below.

```Oldest Active Ticket =
VAR _active =
CALCULATETABLE(
SUMMARIZE(
Incident,
Incident[Ticket Number],
Incident[Unit Name]
),
"start",
CALCULATE(
COUNTROWS( dates ),
DATESBETWEEN( dates[Date], MIN( Incident[CreatedOn_DateKey] ), TODAY() ),
dates[isWeekend] = FALSE()
)
),
KEEPFILTERS( 'Incident'[State Code] = "Active" )
)
RETURN
SUMX( _active, [start] )
```
Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

Announcements

#### CollabDays Lisbon - 26 November 2022

Sessions include practical, hands-on experience that will help you take the next step in your career and know-how.

#### Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

#### European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

#### Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors
Top Kudoed Authors