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.