Forum Discussion

KCfromDC's avatar
KCfromDC
Frequent Visitor
11 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 do *not* want to show the years prior.

 

For example, the table and graph for 10 years looks something like this:

 

 

I want to show only 2020-2024 on a line graph with two lines, one of the actual count and one of the 5 year average (so each 5 year point is an average from 2015-2019, 2016-2020, etc. etc.). Unfortunately, putting the filter causes the calculations to fail, for obvious reasons - it's unable to look more than n-5 years back.

 

 

The graph needs to show what's in the red box, without the 2014-2019 data.

 

 

Any assistance would be greatly appreciated. Thanks!

-K

 

 

 

 

 

Need Help
  • MFelix's avatar
    MFelix
    11 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

7 Replies

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

    Hi KCfromDC ,

     

    You need to change the context of your measure for the PY in order to get the correct calculation. You want to overwrite the filter context.

     

    What is the formula you are using for the 5 years previous average?

     

    • KCfromDC's avatar
      KCfromDC
      Frequent Visitor
      11 months ago

      Hi Miguel-

      Notifications prev 5 years avg = 
VAR selectedyear =
    MAX ( Dates[Year] )
VAR previous5years = selectedyear - 5
VAR newdatetable =
    CALCULATETABLE (
        VALUES ( Dates[Year] ),
        FILTER (
            ALL ( Dates ),
            Dates[Year] < selectedyear
                && Dates[Year] >= previous5years
        )
    )
RETURN
    AVERAGEX ( newdatetable, [Notifications count] )

       

      Notifications count = 
CALCULATE(
    COUNTROWS(Notifications),Dates[Year] = max(Dates[Year])
)

       

      I'm a total noob at this, so appreciate the help. Thank you!

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

        Hi KCfromDC ,

         

        Try to change your calculation to the following:

         

        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] ) - 5
        ),
        [Notifications]
    )

         

        This should return expected result.

  • KCfromDC's avatar
    KCfromDC
    Frequent Visitor
    11 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
      11 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

  • Anonymous's avatar
    Anonymous
    Not applicable
    11 months ago

    Hi KCfromDC ,

    Thank you for reaching out to the Microsoft Fabric Community Forum.

     

    Could you please let us know if the issue has been resolved? I wanted to check if you had the opportunity to review the information provided by MFelix  . If you still require support, please let us know, we are happy to assist you.

     

    Thank you.

  • Anonymous's avatar
    Anonymous
    Not applicable
    11 months ago

    Hi KCfromDC ,

     

    We haven’t received an update from you in some time. Could you please let us know if the issue has been resolved?
    If you still require support, please let us know, we are happy to assist you.

     

    Thank you.