Bar chart with Reference line for previous year average

[BlondeThursday]

I am trying to create a bar chart that counts occurrences per month in the selected year. The customer also wants to have a reference line showing the average for the entire previous year as well. So basically what you would get if you tick the Average trendline box but for the previous year.

So, if the data looks like this: (Reference is unique)

DateIssued	Reference	Category
1/1/17	1	Basic
3/1/17	2	Complex
5/1/17	3	Basic
5/1/17	4	Basic
6/1/17	5	Moderate
8/1/17	6	High
8/1/17	7	High
21/1/17	8	Moderate
3/2/17	12	Basic
3/2/17	34A	Complex
3/3/17	12/5	Basic
6/7/17	ASC36	Moderate
6/7/17	72	High
12/7/17	RRP	Basic
12/11/17	Ri32	Niggling
3/1/18	18-a	Basic
14/1/18	18-b	Basic
14/1/18	18-c	Complex
14/1/18	18-d	High
3/3/18	18/5	Basic
4/3/18	TT3	High

We want Dimensions of Month and Category and an Expression of =Count(Reference).

But also a reference line that, for instance, would be horizontal at .5 (the average count per month for 2017) if the year selected was 2018 and the Category selected was 'Basic'.

I suspect I'm being a bit thick here, but I can't seem to see it. Probably having trouble with aggregating Count for set analysis of where Year = Year-1.

Any steer would be a help. Thank you.

[sunny_talwar · Accepted solution]

Try this expression

Sum({<Year = {$(=Max(Year)-1)}>} TOTAL Aggr(Count({<Year = {$(=Max(Year)-1)}>}Reference), MonthYear))/12

[BlondeThursday]

Thank you very much. I was on the right track  but getting muddled with the set analysis.