topic Re: Need average days to close tickets and Tickets greater than 30 days - DAX math help in DAX Commands and Tips

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

Anonymous — Tue, 12 Nov 2024 17:24:43 GMT

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!

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

Fowmy — Tue, 12 Nov 2024 17:41:29 GMT

Anonymous

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

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

Anonymous — Tue, 12 Nov 2024 19:29:18 GMT

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 🙂

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

Fowmy — Tue, 12 Nov 2024 20:00:35 GMT

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

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

Anonymous — Tue, 12 Nov 2024 20:13:47 GMT

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!)

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

Anonymous — Thu, 14 Nov 2024 06:43:07 GMT

Hi Anonymous ,

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.

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

Anonymous — Thu, 14 Nov 2024 16:37:57 GMT

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.

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

Anonymous — Thu, 14 Nov 2024 16:39:48 GMT

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

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

Anonymous — Thu, 14 Nov 2024 17:30:04 GMT

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