3-year rolling average issue

[lisanspiegelaar]

Hi,

I have two tables:

1. Date table

2. Table containing data on sorting analysis of residual household waste. We look at this to determine the recycle potential. This table contains the percentage of a certain waste stream found in the garbage for a specific waste analysis. These are the columns:

The percentages are calculated for each subcomponent. Some components consist of several subcomponents. The last column groups the components into groups. The date column contains the date of the specific waste analysis.

The two tables are linked through the date column.

The sorting analysis table contains data from 2019 through 2024. For 2019 until 2023, i have one sorting analysis per year, so 1 percentage per subcomponent per year. However, in 2024 I have 3 waste analyses, so 3 percentages per component per year.

I calculated the yearly average using this DAX measure:

Yearly average per subcomponent = 
CALCULATE(
    AVERAGE('Sorteerproeven data SharePoint List'[Percentage (van kilo's)]),
    ALLEXCEPT('Sorteerproeven data SharePoint List', 'Sorteerproeven data SharePoint List'[Grouping components Residual], 'Sorteerproeven data SharePoint List'[Component], 'Sorteerproeven data SharePoint List'[Subcomponent], Datum[Jaar])
)

This gives me the correct result per subcomponent, however, when viewing these numbers in a matrix, the components and grouped components are wrong, since that will also be an average, whearas I need that to be the sum. This measure works to solve that issue:

Yearly average per component = 
SUMX(
    SUMMARIZE(
        'Sorteerproeven data SharePoint List',
        'Sorteerproeven data SharePoint List'[Component],
        'Sorteerproeven data SharePoint List'[Subcomponent],
        "JaarGemiddeldeDeelstroom", [Yearly average per subcomponent]
    ),
    [Yearly average per subcomponent]
)

This last measure gives me the correct yearly average for each subcomponent, component and grouped components.

However, now we arrive at my issue. I want to calculate a 3-year rolling average of those yearly averages. I have tried so many different variations and discussed it endlessly with Copilot, but I ended up in a loop where it kept on giving me the same measures that had issues. 

This measure is the closest to the correct answer for the 3-year RA per subcomponent:

3-year rolling average per subcomponent = 
CALCULATE(
    SUM('Sorteerproeven data SharePoint List'[Percentage (van kilo's)]) / COUNT('Sorteerproeven data SharePoint List'[Percentage (van kilo's)]),
    DATESINPERIOD(
        'Datum'[Date],
        MAX('Datum'[Date]),
        -3,
        YEAR
    ),
    ALLEXCEPT('Sorteerproeven data SharePoint List', 'Sorteerproeven data SharePoint List'[Grouping components Residual], 'Sorteerproeven data SharePoint List'[Component], 'Sorteerproeven data SharePoint List'[Subcomponent])
)

The weird thing is that this gives me the correct 3-year ROA for 2021, 2022 and 2023, where I only have one sorting analysis per year, but it is incorrect for 2024, when I have several sorting analyses per year. I cannot figure out why it isn't working or what the solution is, so I would be very grateful for some help. I have a .pbix file I can share, but I don't know how to attach it to this message.

After having figured out the correct 3-year RA per subcomponent, I still have to get the correct number per component and grouped components, but I am hoping the same SUMX( SUMMARIZE( will work as for the yearly averages. 

Thanks in advance!

Edit:
This is a wetransfer link to the file: https://we.tl/t-8v1ptSUvW6 

[Anonymous]

Hi @lisanspiegelaar 

Please try this measure:

3-year rolling average per subcomponent =
VAR _CurrentYear =
    YEAR ( MAX ( 'Datum'[Date] ) )
VAR Jaargemiddelden =
    SUMMARIZE (
        'Sorteerproeven data SharePoint List',
        Datum[Jaar],
        "JaarGemiddeldeDeelstroom", [Yearly average per subcomponent]
    )
RETURN
    CALCULATE (
        AVERAGEX ( Jaargemiddelden, [JaarGemiddeldeDeelstroom] ),
        FILTER (
            ALLSELECTED ( 'Datum' ),
            YEAR ( 'Datum'[Date] ) <= _CurrentYear
                && YEAR ( 'Datum'[Date] ) >= _CurrentYear - 3
        ),
        ALLEXCEPT (
            'Sorteerproeven data SharePoint List',
            'Sorteerproeven data SharePoint List'[Grouping components Residual],
            'Sorteerproeven data SharePoint List'[Component],
            'Sorteerproeven data SharePoint List'[subcomponent]
        )
    )

Hope this can help.

Best Regards

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

[lisanspiegelaar]

Hi Zhendong, thank you for taking the time to reply! I have tried your measure, however, this gives me the yearly averages rather than the three-year rolling average:

[lisanspiegelaar]

This is another measure I have tried, but this just gives me the yearly average rather than a 3-year RA:

VAR Jaargemiddelden = 
    SUMMARIZE(
        'Sorteerproeven data SharePoint List',
        Datum[Jaar],
        "JaarGemiddeldeDeelstroom", [Yearly average per subcomponent]
    )
RETURN
    CALCULATE(
        AVERAGEX(Jaargemiddelden, [JaarGemiddeldeDeelstroom]),
        DATESINPERIOD(
            'Datum'[Date],
            MAX('Datum'[Date]),
            -3,
            YEAR
        ),
        ALLEXCEPT('Sorteerproeven data SharePoint List', 'Sorteerproeven data SharePoint List'[Grouping components Residual], 'Sorteerproeven data SharePoint List'[Component], 'Sorteerproeven data SharePoint List'[subcomponent])
    )