Average for only one year

Question

Hello to the forum,I have the following table Sales:&nbsp;DateAmount2022-01-01$220,002022-02-01$632,002022-03-01$647,002022-04-01$229,002022-05-01$231,002022-06-01$880,002022-07-01$306,002022-08-01$777,002022-09-01$788,002022-10-01$631,002022-11-01$737,002022-12-01$702,002023-01-01$209,002023-02-01$177,002023-03-01$800,002023-04-01$310,002023-05-01$360,002023-06-01$121,002023-07-01$216,002023-08-01$320,002023-09-01$435,002023-10-01$377,002023-11-01$300,002023-12-01$785,00&nbsp;I connected the Sales table to the Dates table.&nbsp;Now I want to calculate the average sales. My goal is to always calculate the average over exactly one year. This result should come out:&nbsp;Table "Sales" with the dataI want to createTo do this, I create the following measure:Average Sales =CALCULATE(&nbsp; &nbsp; AVERAGE('Sales'[Amount]),&nbsp; &nbsp; ALL(Dates[Month]))I think the expression ALL(Dates[Month]) should now remove the filter context for the month, leaving the filter context for the year. So the result from the 2nd table should appear.&nbsp;In fact, I get this result:&nbsp;The measure does not deliver the result that I expected.Where is my misconception?&nbsp;Thanks!&nbsp;

doleary85 · Accepted Answer

Hi,&nbsp;you could use the same measure you have but instead of ALL use ALLEXCEPT and join only on the year column. e.g.&nbsp;Average Sales =CALCULATE(&nbsp; &nbsp; AVERAGE('Sales'[Amount]),&nbsp; &nbsp; ALLEXCEPT(Sales[Year]))if you don't have a year column:&nbsp;&nbsp;Year = year('sales'[Date])&nbsp;If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Forum Discussion

Average for only one year

2 Replies

Related content

Daily Average Over Multiple Years

Weekly Average

average year sales

Average and Moving Average

moving average help

Date	Amount
2022-01-01	$220,00
2022-02-01	$632,00
2022-03-01	$647,00
2022-04-01	$229,00
2022-05-01	$231,00
2022-06-01	$880,00
2022-07-01	$306,00
2022-08-01	$777,00
2022-09-01	$788,00
2022-10-01	$631,00
2022-11-01	$737,00
2022-12-01	$702,00
2023-01-01	$209,00
2023-02-01	$177,00
2023-03-01	$800,00
2023-04-01	$310,00
2023-05-01	$360,00
2023-06-01	$121,00
2023-07-01	$216,00
2023-08-01	$320,00
2023-09-01	$435,00
2023-10-01	$377,00
2023-11-01	$300,00
2023-12-01	$785,00