Age profile of service desk data over Time

[PCahill19]

Hi, I'm new to PowerBI and looking for some help to solve the following.

I have imported the following data (This is dummy data for pupose of the question) from an Excel file. Showing Ages of service desk tickets month by month. The purpose is to create a Line chart showing age of tickets, either opened or closed within particular months.

Assignment Group	opened	closed	Age	Age to April 30th	Included in April	Age May 31st	Included in May	Age June 30th	Included in June
Group 1	01/02/2022	04/04/2022	62	62	1	0	0	0	0
Group 1	01/04/2022			30	1	61	1	91	1
Group 2	03/05/2022	06/06/2022	34	0	0	28	1	34	1
Group 3	01/07/2022	03/07/2022	2	0	0	0	0	0	0
				92	2	89	2	125	2

I have then created a second table within PowerBI as follows, using measures from the bove table:

	Average Age
April	46
May	44.5
June	62.5

Whilst this works in that I can create a line chart from the new table, I am unable to add a slicer based on the first table, so that I can see the average age of tickets per month for each assignment group, but with the same line chart.

I've been thinking of a new measure to calculate Ticket age per month?

Any help appreciated.

[v-yalanwu-msft · Accepted solution]

Hi, @PCahill19 ;

You could unpovit it in power query .

1. unpovit it.

2.replace "to " to ""

3.split column.

4.remove columns.

The final show:

Then create a measure by dax.

aver = DIVIDE(SUM([Value]),CALCULATE(COUNT('Table'[Month]),'Table'[Value]<>0))

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PCahill19]

Thank you. This has worked.

[PCahill19]

Thank you. I will try this later today.

[apaulso9]

Hello,

If you have yet to you should create a calendar table and link it to your dtata table. Then you can add the average age measure and seperate it per month.