calculate Average for previous year keeping filters on

[Anonymous]

So I am trying to write the query where I need the average of ratings from previous year to compare with current year.

The dataset has dates for past 5 years. How can I get the average for last year, last week compare to this year. I am writing something like this 

LYR =
AVERAGEX(
KEEPFILTERS(VALUES('Table_F'[Inbound_Outbound])),  # this is the filter so ratings are segregated in inbound and outbound
CALCULATE(AVERAGE('[Rating]))

)

[Anonymous]

This is the output I am getting for average for all the years (ca/ea are the inbound and outbound channels) now this is YTD ratings, I need it to split it by year e.g average ratings for current year then append it to avg rating for 2021

[mh2587]

Can you show the desired output in excel by exporting this data in excel

[Anonymous]

Channel	Rating	LY Rating	LY Variance	LW Rating	LW Variance
CA	4.69	4.77	-0.07	4.76	-0.07
EA	4.70	4.86	-0.16	4.87	-0.17
	4.70	4.81	-0.11	4.82	-0.12

[Anonymous]

Hi mh2587, thanks for getting back to me. But it doesnt help with the filters required

[mh2587]

Can elaborate further with your desire output and what filter you required with screenshots or something else

[Anonymous]

So basically I need in columns:

Current avg rating, last year's rating, variance, last week ratings & Variance

[v-kkf-msft]

Hi @Anonymous ,

Please try the following formula:

LY Rating = 
CALCULATE (
    [Rating],
    KEEPFILTERS ( VALUES ( 'Table_F'[Inbound_Outbound] ) ),
    DATESINPERIOD ( Dates[Date], MIN ( Dates[Date] ) - 1, -1, YEAR )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Unfortunately its not working for me and giving me an error

"A single value for column 'Star_Rating' in table cannot be determined. a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

[mh2587]

LY Avg = CALCULATE(AVERAGE(Table[Rating]),DATEADD('Table'[Date],-1,YEAR))