Average Age of Tickets

[jonnychenoweth]

Spoiler

All,

I have read a ton about this topic, which is why I added a field called "Age" as a whole number which is essentially the age of that ticket in days. Looking at the Created date vs. todays date. The issue I can't for the life of me figure out what I am doing wrong to be able to get a bar chart that will give me the average age of tickets on a given month for example. I can get it to show average age by month. However, I did the calculations and what it is doing is giving me the average of of this tickets IN that given month only and NOT all tickets in that month AND previous months as true Average age should be. I wanted it to be a bar chart for example that shows by month the average age of tickets at that time. Meaning open tickets (which is what Age (days) is, how long it has been opened for).

I hope this makes sense, I know I am doing something stupid small to make this not work, keep in mind I am brand new to PowerBi and have been using it a ton but only about a week. If you have any advice let me know. I have a ton more data than what is in this screenshot but I am showing an example line of data with the important columns that could be relevant specifically. Ideas let me know, thank you!

I almost think that if I could add a column that has today's date and every date from then on it might work, but I think it does that automatically in the calculation. So I know something is off.

[jonnychenoweth]

Figured it out finally, key was I needed to have a Date table with all dates, months, years as 3 columns in said table that was unrelated to my data but would allow it to know what todays date was so it had something to compare it to I assume. Took a while but searching other threads and realizing a few things helped. That was my missing piece for sure. Leaving this here even though no one responded in case someone else is working on averages and finds this thread....once I added a table with dates by each date unrelated to my actual data I was able to do it fully. I also verified the data was accurate to what was in Jira average Age reports etc.

[RicoZhou2 · Accepted solution]

Hi @jonnychenoweth ,

I am so glad that you can find the solution by yourself. According to your statement, I think you may want to calcualte the running average age. I suggest you to create a dimdate table with continuous date by CALENDAR() or CALENDARAUTO().

DimDate code:

DimDate = 
ADDCOLUMNS( CALENDARAUTO() ,"Year",YEAR([Date]),"Month",MONTH([Date]),"MonthName",FORMAT([Date],"MMMM"))

Create a relationship between DimDate[Date] and Fact[Date].

Measure:

Measure = 
CALCULATE(AVERAGE('Table'[Age]),FILTER(ALL(DimDate),DimDate[Date]<=MAX(DimDate[Date])))

Best Regards.