Average for only one year

[Jan_Trummel]

Hello to the forum,

I have the following table Sales:

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

I connected the Sales table to the Dates table.

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:

Table "Sales" with the dataI want to create

To do this, I create the following measure:

Average Sales =

CALCULATE(

    AVERAGE('Sales'[Amount]),

    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.

In fact, I get this result:

The measure does not deliver the result that I expected.

Where is my misconception?

Thanks!

[halfglassdarkly]

It might still be inferring context from Dates[Date]. 

Try ALLEXCEPT(Dates[Year]) in place of ALL(Dates[Month]).

Or try using:

AVERAGEX(VALUES(Dates[Year]),Sales[Amount])

[DOLEARY85 · Accepted solution]

Hi,

you could use the same measure you have but instead of ALL use ALLEXCEPT and join only on the year column. e.g.

Average Sales =

CALCULATE(

    AVERAGE('Sales'[Amount]),

    ALLEXCEPT(Sales[Year])

)

if you don't have a year column: 

Year = year('sales'[Date])

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍