Calculating days active cases

[KevinParijs]

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.

[Mariusz · Accepted solution]

Hi @KevinParijs 

You can try something like below.

Oldest Active Ticket = 
VAR _active = 
    CALCULATETABLE(
        ADDCOLUMNS( 
            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.