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

     

JimLee's avatar
JimLee
Helper I
5 years ago

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's avatar
JimLee
Helper I
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 
            
                )
            )
        )
    ))