Rolling Average DAX to plot correctly on Column Chart

[Anonymous]

Hi All,

I have a problem with the measure of the 3mth rolling average to visualise it correctly on the graph. 

The data model is here: 

https://docs.google.com/spreadsheets/d/1naChcuZtjSbk0pVEi1xKuTZhSY7Rpabc0OCbmxowQME/edit?usp=sharing

I am using the formula below to calculate 3mth average 

Product3Mth = CALCULATE(SUM('Table'[Product A uncum]);DATESINPERIOD('Table'[Date];LASTDATE('Table'[Date]);-3;MONTH))/3

When I am plotting it as a table it is showing right values for each month. 

But When I amplotting it in the column chart together with Product A Accumulated I am getting wrong value which is the value for Product unaccum /3 insted of sum of 3 consecutive values for Product unaccum /3.

Take a look. 

What should I change in the DAX to have it visualised correctly?  Please HELP

[v-frfei-msft · Accepted solution]

Hi @Anonymous,

Based on my test, we can take the following steps to meet your requirement.

1. Enter the sample data as you shared and create a Calendar table using the formula.

Calendar = CALENDARAUTO()

2. Create the measure to achieve your goal.

Measure = CALCULATE(SUM('Table'[Product A unaccum]),DATESINPERIOD('Calendar'[Date],MAX('Calendar'[Date]),-3,MONTH))/3

For more details, please check the pbix as attached.

Regards,

Frank

[v-frfei-msft]

Hi @Anonymous,

Does that make sense? If so, kindly mark my answer as a solution to close the case.

Regards,
Frank