Headcount total for the past 12 months

Question

I have the below query which gives me the headcount per month when I select a month from the date table which is fine and works correctly.

I would like the average headcount for the past 12 months from the selected date e.g if i select Aug 2020, the average should be a sum of headcount from July 2019 - Aug 2020

Active Employees = 
var currentdate = 'Date'[Max Date]

RETURN
CALCULATE(
    DISTINCTCOUNT('Joined Query'[EMP_NO]),
    'Joined Query'[DATE_OF_EMPLOYMENT] <= currentdate,
    or(
        ISBLANK('Joined Query'[DATE_OF_LEAVING]),
        'Joined Query'[DATE_OF_LEAVING] > currentdate
    )
)

Answer 1

You can reuse your headcount measure per month like this:

average 12 months =
VAR currentdate = SELECTEDVALUE('Date'[Max Date])

RETURN AVERAGEX(
  SUMMARIZE(
     FILTER(ALL('Date'), 
       'Date'[Max Date] <= currentdate && 'Date'[Max Date] >= EDATE(currentDate,-11)), 
       'Date'[Max Date],
       "headcount", [Active Employees]
  ), [headcount])

Please note, -11 gives you the date 12 months ago in regards to the current selected date. In your question you say if you select August you want to see from July last year, and that would actualy be 14 months, not 12. If that's your use case you need to change it to -13.

Answer 2

Create 2 following measures for 12 month start and end date-

12 Month End Date = 
    
VAR D1 = 
    DATEVALUE(
        SELECTEDVALUE('Date'[Max Date].[MonthNo]) 
        & "/1/" 
        & SELECTEDVALUE('Date'[Max Date].[Year])
    )    

RETURN D1-1

12 Month Start Date = 

VAR D1 = 
    DATEVALUE(
        SELECTEDVALUE('Date'[Max Date].[MonthNo]) 
        & "/1/" 
        & SELECTEDVALUE('Date'[Max Date].[Year])
    )

RETURN EDATE(D1,-12) 

Now create this following measure for average calculation-

12_month_average = 
(
    CALCULATE(
        DISTINCTCOUNT('Joined Query'[EMP_NO]), 
        DATESBETWEEN(
            'Date'[Max Date],
            [12 Month Start Date],
            [12 Month End Date]
        )
    ) + 0
) / 12