Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

3 Year Average per Month

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 tabl...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • suparnababu8's avatar
    suparnababu8
    1 year ago

    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

danextian's avatar
danextian
Icon for Super User rankSuper User
1 year ago

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.