How to get an average for the previous 12 months dynamically

[ZakharyMG]

Evening gentlemen, 

I have this problem and I am not sure how to approach it. I have a table full of consumer complaints for our company, ofcourse the consumer complaint has a submission date. 

One of the ways we weight and average consumer complaints is by the volume produced by our factory. One of these measures is Consumer Complaints per Million units produced. Where this measure for any given month X is the consumer complaints from month X divided by the average units produced in months ranging from X-12 to X-1 (the average of the 12 months/year before)

So for each month I need to calculate the average volume for the 12 months before it. I am unsure how to do this in PowerBi and my knowledge in dax is limited. Currently I have a date dimension table that is just CalendarAuto()  and this is how I bridge between the consumer complaints and the volume produced table, but I can't figure out how to dynamically perserve the 12 months before the month that is selected in the slicer (this is for the volume table only).

Any help would be much appreciated.

[adamc-powerbi · Accepted solution]

Hi,

Your data table seems fine. You will need a schema like so:

Date 1-to-* complaints table

Date 1-to-* volume product table

And then to calculae the 12 month average, look at this article:
https://www.sqlbi.com/articles/rolling-12-months-average-in-dax/