Rolling Average Issues

Hi hungry_learner ,

There is nothing wrong with the expression, in your logic you need to specify country by SELECTEDVALUE and then calculate the average over three years. That means it doesn't work when you don't select country. Perhaps you need to use HASONEFILTER(). Also, you need to calculate another part of the logic that needs to be displayed when unselected.
HASONEFILTER function (DAX) - DAX | Microsoft Learn

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

hungry_learner 

Rolling Average 36M = 
CALCULATE(
AVERAGEX(
FILTER(
ALL('3-Year Data'),
'3-Year Data'[Country] = SELECTEDVALUE('3-Year Data'[Country]) &&
'3-Year Data'[Date] <= MAX('3-Year Data'[Date]) &&
'3-Year Data'[Date] > EDATE(MAX('3-Year Data'[Date]), -36)
),
'3-Year Data'[Value]
)
)

Ensure that the Date column in '3-Year Data' is properly formatted as a date.

Use '3-Year Data'[Date] as the X-axis to plot the rolling average over time.

Hi,

You should first of all create a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name by the Month number.  Create a relationship (Many to One and single) from the Date column of your Fact table to the Date column of the Calendar Table.  To your visual/slicers/filters, add Year, Month from the Calendar Table.  Modify your measure to:

Total = SUM('3-Year Data'[Value])

Measure = AVERAGEX(DATESBETWEEN(Calendar[date],MAX(Calendar[Date]),EDATE(MAX('3-Year Data'[Date]), -36)),[Total])

Hope this helps.