Need average days to close tickets and Tickets greater than 30 days - DAX math help

[Hopeisgood1]

Hi,

Bear with me. I am not great at DAX math. 

I have 2 formulas I need help with. 

First - Over 30 days. I got this far but where am I off? It's not populating the YES/NOW in the column...

Over 30 Days = IF(INC[PBI Submit Date].[Date]>=TODAY(),IF(INC[PBI Submit Date].[Date]<=Today(), "yes", "no"))

Secondly - Average days to close tickets....my mind is blank on this one....

 Thank you for all your help!

[Hopeisgood1 · Accepted solution]

Hi Yilong!

Thank you! So Close!!!

However, what I am now seeing is this...

I need to add 3 values - Closed, Resolved, and Cancelled - from the Status column (INC[Status]) to the formula that should filter them. I tried and it's not filtering them out. Below are the Resolved tickets (sigh) I tried the filter into the formula but I am not getting it right at all.

Over 30 Days =

IF (

    'INC'[PBI Closed Date] - 'INC'[PBI Submit Date]

        < ( TODAY () - 30 )

        && 'INC'[Status*]<> "Resolved"

        && 'INC'[Status*]<> "Closed"

        && 'INC'[Status*]<> "Cancelled",

     "No",

    "Yes"

)

Then I did this and it worked somewhat - 

Over 30 Days =

IF (

    'INC'[PBI Closed Date] - 'INC'[PBI Submit Date]

        < ( TODAY () - 30 )

        && 'INC'[Status*]<> "Resolved"

        || 'INC'[Status*]<> "Closed"

        || 'INC'[Status*]<> "Cancelled",

     "No",

    "Yes"

)

BUT I cannot get the Yes for those over 30 days for those in Assigned, Pending, and In Progress status.

[Hopeisgood1 · Accepted solution]

I figured it out!

I had to revamp a couple columns.

1st) PBI Aging  

PBI Aging = (TODAY()-INC[PBI Submit Date].[Date])

2nd) Over 30 Days

Over 30 Days =

    (IF(INC[PBI Aging] >= 30, "Yes",

    IF(INC[PBI Aging] < 30, "No")))

3rd) created a card / filtered out the closed/Cancelled/Resolved tickets and then filtered to YES/Counted by Active tickets

[Hopeisgood1]

Lawd! I accidently hit the Accepted as Solution by mistake!!! How do I take that out??

[Fowmy]

@Hopeisgood1 

You need to add these as calculated column, NOT as measures:

Over 30 Days = IF ( INC[PBI Submit Date].[Date] <  ( TODAY () - 30 ), "Yes", "No" )

Average days to close tickets = INT ( INC[PBI Closed Date].[Date] -  INC[PBI Submit Date].[Date] )

[Hopeisgood1]

For over 30 days, I am noticing those with NULL  or blank in the closed date that they have been resolved in the past - how do I exclude those closed date nulls with the status or Resolved, Cancelled, and Closed?

These are tickets with bad data entry from the past 😤

Currently working on the Average days piece as there are some weird things in there and trying to find the root cause of it.

Thanks 🙂

[Fowmy]

Over 30 Days = IF ( INC[PBI Submit Date].[Date] <  ( TODAY () - 30 ) && 
INC[PBI Close Date].[Date] <> Blank()
, "Yes", "No" )

[Hopeisgood1]

Resolved status fixed...

Though why is this still stating YES for some that are not over 30 days for the Closed status? All of these rows you are seeing are in closed status. (Nevermind the PBI Aging as I am working on that one!)

[v-yilong-msft]

Hi @Hopeisgood1 ,

I create a table as you mentioned.

So I think you can change your DAX code and you can try this DAX code:

Over 30 Days = 
IF (
    'INC'[PBI Closed Date] - 'INC'[PBI Submit Date]
        < ( TODAY () - 30 )
        && 'INC'[PBI Closed Date] <> BLANK (),
    "No",
    "Yes"
)

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Hopeisgood1 · Accepted solution]

Hi Yilong!

Thank you! So Close!!!

However, what I am now seeing is this...

I need to add 3 values - Closed, Resolved, and Cancelled - from the Status column (INC[Status]) to the formula that should filter them. I tried and it's not filtering them out. Below are the Resolved tickets (sigh) I tried the filter into the formula but I am not getting it right at all.

Over 30 Days =

IF (

    'INC'[PBI Closed Date] - 'INC'[PBI Submit Date]

        < ( TODAY () - 30 )

        && 'INC'[Status*]<> "Resolved"

        && 'INC'[Status*]<> "Closed"

        && 'INC'[Status*]<> "Cancelled",

     "No",

    "Yes"

)

Then I did this and it worked somewhat - 

Over 30 Days =

IF (

    'INC'[PBI Closed Date] - 'INC'[PBI Submit Date]

        < ( TODAY () - 30 )

        && 'INC'[Status*]<> "Resolved"

        || 'INC'[Status*]<> "Closed"

        || 'INC'[Status*]<> "Cancelled",

     "No",

    "Yes"

)

BUT I cannot get the Yes for those over 30 days for those in Assigned, Pending, and In Progress status.