Forum Discussion

KCfromDC's avatar
KCfromDC
Frequent Visitor
10 months ago
Solved

Hide previous years on x axis but keep average calculation

Hi all- I have a line graph with a count of items and a 5 year average (method here). I want to show a line graph of only the trends from the previous 5 years (e.g. count and then the average), but ...
Need Help
  • MFelix's avatar
    MFelix
    10 months ago

    Hi KCfromDC ,

     

    Apologies for the incorrection I have done - 5 years on the formula and should be minus -4 years  to get current year + 4 that gives 5 years.

     

    If you do the math:

    105 + 136 + 123 + 90 +122 +107 = 683 / 6 = 113.83

     

    Redo the formula to:

    Notifications prev 5 years avg =
VAR _TempTable =
    ADDCOLUMNS (
        CALCULATETABLE (
            VALUES ( 'Dates'[Year] ),
            REMOVEFILTERS ( 'Dates'[Year] )
        ),
        "Notifications", [Notifications count]
    )
RETURN
    AVERAGEX (
        FILTER (
            _TempTable ,
            'Calendar'[Year] <= MAX ( 'Dates'[Year] )
                && 'Calendar'[Year] >= MAX ( 'Dates'[Year] ) - 4
        ),
        [Notifications]
    )

     

    This should get expected result

KCfromDC's avatar
KCfromDC
Frequent Visitor
10 months ago

Thanks! I changed the Return function to be 'Dates'[Year] as that's my date table. However, it's giving me the wrong values. 

 

Here's what I get in Excel (using values back to 2010)

YearCount5 year average
2010105 
2011136 
2012123 
201390 
2014122 
2015107115.2
2016130115.6
2017130114.4
2018150115.8
2019134127.8
2020152130.2
2021185139.2
2022134150.2
2023180151
2024176157

 

The below is from PowerBI. The original column is calculating correctly from 2015-2024 (since it's not able to "see" the 2014 and earlier 5 year average, e.g. 2009-2013, 2008-2012, etc.), but the new column using the code you provided is incorrect. 🤔

 

 

This is a tough one, I appreciate the assistance!

 

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    10 months ago

    Hi KCfromDC ,

     

    Apologies for the incorrection I have done - 5 years on the formula and should be minus -4 years  to get current year + 4 that gives 5 years.

     

    If you do the math:

    105 + 136 + 123 + 90 +122 +107 = 683 / 6 = 113.83

     

    Redo the formula to:

    Notifications prev 5 years avg =
VAR _TempTable =
    ADDCOLUMNS (
        CALCULATETABLE (
            VALUES ( 'Dates'[Year] ),
            REMOVEFILTERS ( 'Dates'[Year] )
        ),
        "Notifications", [Notifications count]
    )
RETURN
    AVERAGEX (
        FILTER (
            _TempTable ,
            'Calendar'[Year] <= MAX ( 'Dates'[Year] )
                && 'Calendar'[Year] >= MAX ( 'Dates'[Year] ) - 4
        ),
        [Notifications]
    )

     

    This should get expected result