3 Year Average per Month

[Anonymous]

Hi All,

I am trying to work out an average of X Value over the previous 3 years, not including the current year, by month and then filter by site. In my actual data I have 30+ sites.

My table looks like this:

Site	Month	Month No	Year	Date	Average Windspeed
Bicker Fen	Jan	1	2024	01/01/2024	7.912230769
Bicker Fen	Feb	2	2024	01/02/2024	6.938166667
Bicker Fen	Mar	3	2024	01/03/2024	6.656153846
Bicker Fen	Jan	1	2021	01/01/2021	6.51
Bicker Fen	Feb	2	2021	01/02/2021	7.2
Bicker Fen	Mar	3	2021	01/03/2021	6.4
Bicker Fen	Jan	1	2022	01/01/2022	6.4
Bicker Fen	Feb	2	2022	01/02/2022	9.5
Bicker Fen	Mar	3	2022	01/03/2022	5.7

I've tried  a few different ways like average for windspeed where year is <2024 etc but to no luck.

Thanks in Advance,
M

[suparnababu8 · Accepted solution]

Hi @Anonymous 

Try with this measure

AverageWindspeedLast3Years = 
CALCULATE(
    AVERAGEX(
        VALUES('Table'[Date]),
        'Table'[Average Windspeed]),
    FILTER(
        'Table',
        'Table'[Year] < YEAR(TODAY()) &&
        'Table'[Year] >= YEAR(TODAY()) - 3),
    ALLEXCEPT('Table', 'Table'[Site], 'Table'[Month], 'Table'[Month No])
)

Let me know if it works

[Anonymous]

Hi @suparnababu8,

This worked perfectly! Thank you 🙂

[danextian]

Hi @Anonymous 

Create a year table, relate it to your fact and create this measure:

Average = 
AVERAGEX (
    ADDCOLUMNS (
        SUMMARIZE (
            --create a table of years wherein the years are from current year -3 to current year - 1
            --note: if there are only two years in the current context, the sum of avg windspeed will be divided  by two as well, one if one...etc
            FILTER (
                ALL ( Years ),
                Years[Year]
                    >= MAX ( Years[Year] ) - 3
                    && Years[Year] < MAX ( Years[Year] )
            ),
            Years[Year]
        ),
        "Windspeed", CALCULATE ( SUM ( 'Table'[Average Windspeed] ) )
    ),
    [Windspeed]
)

Mar 2024: 5.7+6.4 = 12.1/2 = 6.05

Please see attached sample pbix.