Forum Discussion

hungry_learner's avatar
hungry_learner
Frequent Visitor
1 year ago
Solved

Rolling Average Issues

Hi All,   Im trying to obtain rolling Average with the measure for below sample data:   Date Month Year Country Value 01-06-2021 June 2021 abc 0 01-07-2021 July 2021 abc 77.1...
Need Help
  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    1 year ago

    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]
    )
)

     

Ashish_Mathur's avatar
Ashish_Mathur
Super User
1 year ago

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.