Charting Aging Tickets Over Time

[JimLee]

I am having trouble charting aging tickets over time. I can do it in Excel, but I cannot figure out how to do it in Power BI. Here is what the data look like:

I can create a table in Excel that calculates the age of each ticket, ticket average, then chart that data. Since Excel can use formulas as headers, I can drive the calculations off the table's headers.

I would rather not have to use Excel as an interum step and just import the raw data in the first table.

Is there a way to do that in Power BI?

[v-janeyg-msft]

Hi, @JimLee 

It’s my pleasure to answer for you.

According to your description, I think modify the syntax in your measure .

Like this:

Open Incidents =
CALCULATE (
    COUNTX (
        FILTER (
            'Incident Table',
            'Incident Table'[Submit Date] <= MAX ( 'Dates'[Date] )
                && OR (
                    ISBLANK ( 'Incident Table'[Last Resolved Date] ),
                    'Incident Table'[Last Resolved Date] > MAX ( 'Dates'[Date] )
                )
        ),
        'Incident Table'[Incident Number]
    ),
    CROSSFILTER ( 'Incident Table'[Submit Date], 'Dates'[Date], NONE )
)

If you have other questions, please feel free to ask me.

Best Regards

Janey Guo

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

[JimLee]

@v-janeyg-msft 

Thank you for helping clean up my ticket count.

What I really need is to be able to track the ticket's age over time. 

For example, in the data is INC000003301893 that was opened on Oct 3. I would like to be able to calculate is how old that ticket is on Oct 4, Oct 5, Oct 6, and so on. This will allow me to get the average ticket age for all open tickets during each period. 

Is there a way to do that? 

[v-janeyg-msft]

Hi, @JimLee 

I don’t know why you use crossfilter, you can try this if it works:

Open Incidents = 
CALCULATE (
    COUNTX (
        FILTER (
            'Incident Table',
            'Incident Table'[Submit Date] <= MAX ( 'Dates'[Date] )
           && 'Incident Table'[Submit Date] >= MIN ( 'Dates'[Date] )
                && OR (
                    ISBLANK ( 'Incident Table'[Last Resolved Date] ),
                    'Incident Table'[Last Resolved Date] > MAX ( 'Dates'[Date] )
                )
        ),
        'Incident Table'[Incident Number]
    )
    //,
   // CROSSFILTER ( 'Incident Table'[Submit Date], 'Dates'[Date], NONE )
)

avg = DIVIDE([Open Incidents],DISTINCTCOUNT(Dates[Date]))

If the problem isn’t solved,please feel free to ask me.

Best Regards

Janey Guo

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

[JimLee]

@v-janeyg-msft 

Thanks for continuing to try and help. 

The Open Incidents yielded zero results. I commented out this line and got the right counts:

&& 'MITSC RES - Last 6 Months Incid'[Submit Date] >= MIN ( 'Dates'[Date] )

Unfortunately, this gives the average count of tickets, but it does not work to get the average age of the tickets:

avg = DIVIDE([Open Incidents],DISTINCTCOUNT(Dates[Date]))

I need to be able to do something like a DATEDIFF using 'Dates'[Date] and [Submit Date], but I cannot figure out how to do that. 

[JimLee · Accepted solution]

I figured it out! Days was not returning partial days, so I had to use hours and divide by 1440. 

Now the problem is the calculations are too hard for my computer's resources! Pardon the messy DAX. 

Average Ticket Age = AVERAGEX('Incidents',
    IF(
    	DATEDIFF('Incidents'[Submit Date],MAX(Dates[Date and Time]),MINUTE)<0, //Submit after reporting date is a neg #, so ticket is not open yet
    "",
        IF(
            ISBLANK('Incidents'[Last Resolved Date]),//if the resoved date is blank, then it is max date minus submit date
        DATEDIFF('Incidents'[Submit Date],MAX(Dates[Date and Time]),MINUTE)/1440,
            IF(
                'Incidents'[Last Resolved Date]>MAX(Dates[Date and Time]),
            DATEDIFF('Incidents'[Submit Date],MAX(Dates[Date and Time]),MINUTE)/1440,
                IF(
                    'Incidents'[Last Resolved Date]+1>MAX(Dates[Date and Time]),DATEDIFF('Incidents'[Submit Date],'Incidents'[Last Resolved Date],MINUTE)/1440,
                ""//If the resolved date is greater than the report date then the age is the report date minus submit date 
            
                )
            )
        )
    ))

[Ashish_Mathur]

Hi,

Will the closed and open dates always be the first date of every month?  Also, why do the dates in the header range stop till July 1?

[JimLee]

@Ashish_Mathur 

Thank you for responding. 

The dates are in mm/dd/yyyy format, so the open and closed dates in the example are throughout the month. I just created a quick table as an example of the data, but really there are thousands of tickets that span about two years. 

[Ashish_Mathur]

Hi,

Well then take a realistic example and on that example show the exact result you are expecting. 

[JimLee]

If the data are easier to work with, then here they are.

https://1drv.ms/x/s!AgfXT0xck6ZogowmXVxPzY9raoIYLA?e=35hFhj

[amitchandak]

@JimLee , Please refer my HR Blog that deals with this kind of data.

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

like :https://www.youtube.com/watch?v=e6Y-l_JtCq4&t=335s

Also in file attached after signature , I added some new calculations

[JimLee]

@amitchandak 

Thank you for the post. I will have to review the data and DAX closer, but the example is calcuating the number of employees for each period. I am looking for the average tenure of all active employees for each period. 

I can get the count of tickets by using:

Total Incidents = COUNTROWS('Incident Table')

Open Incidents = CALCULATE([Total Incidents],
FILTER(VALUES('Incident Table'[Submit Date]),'Incident Table'[Submit Date]<=MAX('Dates'[Date])),
FILTER(VALUES('Incident Table'[Last Resolved Date]),'Incident Table'[Last Resolved Date]>=MIN('Dates'[Date])||'Incident Table'[Last Resolved Date]=0))

But your DAX was much cleaner, so I used:

Open Incidents = CALCULATE(COUNTx(FILTER('Incident Table,'Incident Table'[Submit Date]<=max('Dates'[Date]) && (ISBLANK('Incident Table'[Last Resolved Date]) || 'Incident Table'[Last Resolved Date]>max('Dates'[Date]))),('Incident Table'[Incident Number])),CROSSFILTER('Incident Table'[Submit Date],'Dates'[Date],None))

Any idea how I can get the employee tenure per period using your example?