Rolling 6 Month Average Column Chart

[pstp]

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!

[v-yalanwu-msft · Accepted solution]

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.

[amitchandak]

@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]

@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.