Aged tickets calculation using measure

[Anonymous]

Hello, 

How can I calculate all of the currently open tickets that have been open longer than 30 days using a measure? 

Tables used: Date calendar table + All tickets table (Includes created time, Request ID, and Request Status) 

Can you please help? 

[amitchandak]

@Anonymous ,

We can have a column like

Age  = datediff([Open Date], today(), day)

And then open is age > 30 , close date is null or Request Status ="Open"

or measure

Age  = datediff(min([Open Date]), today(), day) // need to use ticket context to get the age.

Another way deal with it like this HR blog

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

[harshnathani]

Hi @Anonymous ,

You can create a colum

daysOld = DATEDIFF(table[Created Date], TODAY(), DAY)

And then you can create another calculated column for the categories:

daysOldCategory = 
SWITCH(
TRUE(),
table[daysOld] < 15 && table[Status] = "Open", "Less than 15 days", 
table[daysOld] <= 30 && table[Status] = "Open", "15-30 days",
table[daysOld] <= 60; && table[Status] = "Open","30-60 days",
table[daysOld] > 60 && table[status] = "Open", "60 days and above"
)

Else

Create a measure

MEASURE =

var datedi = DATEDIFF(MAX(Table[OpenDate]), TODAY(), DAY)

RETURN

COUNTX( FILTER (Table, Table[Status] = "Open" && datedi >= 30) , Table[Requestid])

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Anonymous]

@harshnathani Hello, 

Thank you for your response. 

I have applied your fix but it did not work and I got an error. 

I might have forgotten that I already have a column called "Days open" calculating how long this ticket has been open since created time. 

So I would like to get those tickets that have been open for longer than 30 days and not closed yet!

Following your measure, I tried the below measure but still got an error 😕

Aged Tickets = COUNTX(FILTER ('All Tickets', 'All Tickets'[Request ID] = "Open" && "Awaiting Customer Feedback") , 'All Tickets'[Days open]>= 30)

[harshnathani · Accepted solution]

Hi @Anonymous ,

Aged Tickets =
COUNTX (
    FILTER (
        'All Tickets',
        'All Tickets'[Request ID]
            IN {
            "Open",
            "Awaiting Customer Feedback"
        }
            && 'All Tickets'[Days open] >= 30
    ),
    'AllTickets'[Request Id]
)

Or

Aged Tickets =
COUNTROWS (
    FILTER (
        'All Tickets',
        'All Tickets'[Request ID]
            IN {
            "Open",
            "Awaiting Customer Feedback"
        }
            && 'All Tickets'[Days open] >= 30
    )
)

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Anonymous]

@harshnathani Thank you so much this worked well. 

I have created a new column and put it as a filter on the page which reflected on all visuals. 😄 

One visual got impacted though - Where is Created tickets last month and the days open >= 30 days 

I tried the below measure and it did not work:

Aged_tickets_per_month = CALCULATE(COUNTA('All Tickets'[Request ID] IN Current Month = MONTH(NOW())-1), 'All Tickets'[Days open] >= 30)

[harshnathani]

HI @Anonymous ,

See if this works

Aged_tickets_last_month =
CALCULATE (
    COUNTX (
        FILTER (
            'All Tickets',
            'All Tickets'[Days Open] >= 30
        ),
        'All Tickets'[Request ID]
    ),
    DATEADD (
        Calendar[Dates],
        -1,
        MONTH
    )
)

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Anonymous]

it still gives me the below error:

[harshnathani]

Hi @Anonymous ,

please share sample data in text format and the expected output.

Also, please share the snapshot of your data model.

Regards,

HN