Divide values by average of a period of time

[Anonymous]

Hi,  I currently have a bar chart.

- X axis: Year-Month 

- Y axis: count 

- Legend: categories 

Where at each x value I have multiple bars, one for each category. I have to divide the count value by the average value of that specific category. I have achieved this with the following DAX command: 

DIVIDE(
SUM(programs[count]),
CALCULATE(AVERAGE(programs[count]), ALLSELECTED(programs[date]))
)

The problem is that I need to divide all the values by the average of the last three months. I have tried the following but it does not take into account different groups, it calculates the average of the last 3 months but even though the SUM(programs[count]) filters by category automatically, the average is calculated on all categories: 

velocity_variance =
VAR latest_date = 21.06
VAR margin = 3
VAR start_date = CONVERT(latest_date - margin, STRING)
VAR end_date = CONVERT(latest_date , STRING)
VAR velocity = DIVIDE(SUMX(FILTER(programs, AND(programs[data] >= start_date, programs[date] < end_date)), programs[count]), margin)
RETURN
DIVIDE(
SUM(programs[count]),
velocity
)

Based on this query, I can only think of creating a different average measure for each category but I don't want this because categories will change in the future and I have a lot. 

The programs table is of the shape: 

Date	Count	Category
20.01	34	A
...	...
21.12	654	C

I hope someone can help me!

(sorry about the code, the editor wouldn't let me add syntax highlighting) 

Thanks.

[amitchandak · Accepted solution]

@Anonymous , Not very clear to me.

For Avg of last 3 months you can try like

example

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

[Anonymous]

Okey, I think the problem is solved, thanks for your help 🙂

[Anonymous]

@amitchandak Hi, thanks for your help, sorry for the explanation I will explain it better: 

1. I need to create a bar chart where each value is divided by the average

2. I have done this but I need that the average is only of the last 3 months 

3. If I try your method it works but it does the average on all categories not each category individually This is what happens, every category has the same average