Forum Discussion

pstp's avatar
pstp
Frequent Visitor
5 years ago
Solved

Rolling 6 Month Average Column Chart

Hello, I'm hoping to create a column chart with 12 months of data displayed for the past year (June 21 - July 20). Each column would display an average of that month and the 5 months preceding it (June '21 column would be average of Jan '21-June '21). Ideally this would be visually set up so 2021 months are one color and 2020 columns are another. Is this achievable with measures or another method? Thank you!

Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    5 years ago

    Hi, pstp ;

    Base on my understand; if you want to calucate avgerge of  last 6 month (such as  1.21-6.21),you could create a measure as follows:

    Avg =
VAR _datedif =
    DATEDIFF ( MAX ( [Date ] ), EOMONTH ( MAX ( [Date ] ), 0 ), DAY )
RETURN
    CALCULATE (
        AVERAGE ( [AtdSum] ),
        FILTER (
            ALL ( 'Table' ),
            [Date ] <= MAX ( [Date ] )
                && [Date ]
                    >= EOMONTH ( [Date ], -5 ) - _datedif
        )
    )

     If you want to display different colors according to the year, you can create another measure and apply the conditional format as shown in the figure：

    flag= IF(YEAR(MAX([Date ]))=2020,1,0)

    The final output is shown below:

    If it's not right, please  provide more logic and share a simple sample file without any sesentive information.

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    pstp , with help from a date table try measures like

     

    Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-6,MONTH))

     

    or

     

    Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),0),-6,MONTH))

     

    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

    • pstp's avatar
      pstp
      Frequent Visitor
      5 years ago

      amitchandak Thank you for responding. I've gotten that far and have a calculation working like that. What I'm stuck on is displaying the same calculation for multiple dates. Included below is the simple data set I'm working with and the desired look of the visual. 

       

       

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, pstp ;

    Base on my understand; if you want to calucate avgerge of  last 6 month (such as  1.21-6.21),you could create a measure as follows:

    Avg =
VAR _datedif =
    DATEDIFF ( MAX ( [Date ] ), EOMONTH ( MAX ( [Date ] ), 0 ), DAY )
RETURN
    CALCULATE (
        AVERAGE ( [AtdSum] ),
        FILTER (
            ALL ( 'Table' ),
            [Date ] <= MAX ( [Date ] )
                && [Date ]
                    >= EOMONTH ( [Date ], -5 ) - _datedif
        )
    )

     If you want to display different colors according to the year, you can create another measure and apply the conditional format as shown in the figure：

    flag= IF(YEAR(MAX([Date ]))=2020,1,0)

    The final output is shown below:

    If it's not right, please  provide more logic and share a simple sample file without any sesentive information.

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.