Forum Discussion
Rolling Average DAX to plot correctly on Column Chart
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
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))/3For more details, please check the pbix as attached.
Regards,
Frank
2 Replies
v-frfei-msftCommunity Support
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))/3For 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