Calculate a rolling average using measures in power pivot

[misstoffeepenny]

Hi all, I've been trying to calculate the rolling average of 'employee count' to use in within another calculation but the formula I have doesn't appear to take the last 12 months but the entire column average, any help appreciated.
Formula:
=CALCULATE((sum('Accident Log'[Count RIDDOR])/AVERAGE(EmployeeCount[Employee Count])*100000),DATESINPERIOD('Calendar'[Date],Max('Calendar'[Date]),-12,MONTH))

Note all my tables have linked relationships and link through a calendar table.

 

[Matt Allington]

Are your “Accident log” and “employee count “ tables both joined to the calendar? Are you using columns from your calendar in your visual and not date columns from your other 2 tables?

 

[misstoffeepenny]

Hi Matt, yes all my tables are linked to the calendar & I’ve only used the calendar date columns to make the pivot tables

 

[Matt Allington]

This formula will take the dates in the visual, then extend those dates to include the previous 12 months. What I suggest you do is write a test formula to help you "see" what is happening. I have modified your formula here so that it will return the first and last date in the current filter context from you visual. I hope it makes sense.

=
CALCULATE (
    (
        "Min: " & MIN ( 'Calendar'[Date] ) & " Max: "
            & MAX ( 'Calendar'[Date] )
    ),
    DATESINPERIOD ( 'Calendar'[Date], MAX ( 'Calendar'[Date] ), -12, MONTH )
)