Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

12 month rolling average with date hierarchy

Hi,

 

I want to show a graph of rolling 12 month trend from 2021 to 2022. 

 

My data is as follow: 

 SalesRolling 12 month
Jan-21       15,740                  20,916
Feb-21       22,108                  22,034
Mar-21       19,559                  21,390
Apr-21       16,361                  20,677
May-21       21,038                  20,517
Jun-21       18,152                  20,054
Jul-21       16,546                  19,644
Aug-21       22,628                  19,511
Sep-21       23,422                  19,761
Oct-21       27,132                  20,378
Nov-21       23,814                  20,488
Dec-21       19,453                  20,496
Jan-22       18,577                  20,733

 

Example of outcome : 

What i get in Powerbi, unable to combine two years. 

 

Correct graph to display, using excel

 

 

Problem: I am using date hierarchy. My other charts are heavily rely on date hierarchy and I do not wish to delete it. How do I create graph of rolling trend with date hierarchy? Thanks.

 

 

 

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    I guess you want dynamic filter last 12 months?

    If so, you could create a measure like below.

    Measure =
    var _axis = format(SELECTEDVALUE('Table'[yearmonth]),"yyyymm")
    var _max = FORMAT(MAXX(ALLSELECTED('Table'),'Table'[yearmonth]),"YYYYMM")
    var _min = FORMAT(EDATE(MAXX(ALLSELECTED('Table'),'Table'[yearmonth]),-11),"YYYYMM")
    return
    if(_axis>=_min&&_axis<=_max,1,0)
    Then add this measure to visual filter set value = 1.
     
    Best Regards,
    Jay

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    Please put date hierarchy in axis field, sales to column value and rolling average to line value.

    Then drill down to the lowest level of data.

    Result would be shown as below:

     

    Best Regards,

    Jay

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi, 

       

      Thanks for your advice! How do I filter only 12 months in this graph? I want remove Jan 2021 and Feb 2022 onwards.

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi Anonymous ,

         

        I guess you want dynamic filter last 12 months?

        If so, you could create a measure like below.

        Measure =
        var _axis = format(SELECTEDVALUE('Table'[yearmonth]),"yyyymm")
        var _max = FORMAT(MAXX(ALLSELECTED('Table'),'Table'[yearmonth]),"YYYYMM")
        var _min = FORMAT(EDATE(MAXX(ALLSELECTED('Table'),'Table'[yearmonth]),-11),"YYYYMM")
        return
        if(_axis>=_min&&_axis<=_max,1,0)
        Then add this measure to visual filter set value = 1.
         
        Best Regards,
        Jay
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks for the advice. I have changed my questions. 

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        4 years ago

        Thank you for the sample data. Please explain what "Rolling 12 month"  mean for you. Is it the current month plus 11 month back?  Is it a sliding window around the current month? For example - which formula do you use to arrive at the 20916 value for January 2021 ?

         

        Also - i just noticed - your primary Y axis starts at 0 but your secondary y axis starts at 3K.  In the visual settings pin the start of the secondary y axis to 0 as well.