Forum Discussion

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

    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?