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

#### Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

#### Power BI March 2023 Update

Find out more about the March 2023 update.

#### March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors