Rolling Average Issues

Hi hungry_learner ,

The issue you're facing lies in this part of your DAX formula:

SELECTEDVALUE('3-Year Data'[Country])

This function works perfectly when a single value is selected in the slicer for Country, but if no selection is made (or multiple values are selected), SELECTEDVALUE returns BLANK. This is why your line chart doesn't display any data when no country is selected—it is expecting a single country to be selected to calculate the rolling average.

The SELECTEDVALUE function only returns a value when exactly one item is selected in the slicer. If:

• No selection is made, the function returns BLANK.
• Multiple selections are made, the function also returns BLANK.

In your case, the rolling average calculation depends on a specific country being selected. When no selection is made, the formula fails to evaluate because SELECTEDVALUE is returning BLANK.

you can default to calculating the rolling average across all countries when no country is selected.

CALCULATE(
    AVERAGEX(
        FILTER(
            ALLSELECTED('3-Year Data'),
            (ISBLANK(SELECTEDVALUE('3-Year Data'[Country])) || 
            '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]
    )
)