Forum Discussion

JimLee's avatar
JimLee
Helper I
5 years ago
Solved

Charting Aging Tickets Over Time

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 ...
Need Help
  • JimLee's avatar
    JimLee
    5 years ago

    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's avatar
Ashish_Mathur
Super User
5 years ago

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's avatar
JimLee
Helper I
5 years ago

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.