DAX Headcount Formula

[mrmmickle1]

I'm new to DAX and just trying to get some basic calculations running. I've looked at a number of references online (DAX Patterns, PowerPivotPro, etc...) but am unsure what I'm doing incorrectly.

My data has EOQ Datestamps in a single field: 'Headcount Files'[Q_End_Date]

So for example if we had 100 Employees at our company at the end of last quarter I would have 100 records with the date 06/30/2017 in the 'Headcount Files'[Q_End_Date].

The max date in this field is 6/30/2017

In the file I'm working with we have 9 Quarters of Data. I would like to calculate the headcount at different time periods....i.e. Headcount Current Quarter, Headcount Previous Quarter, Headcount End of Previous Year, Headcount End of 2015 (2 Years ago).

I thought that if I got one scenario working I could figure it out. Trying to get the Headcount at the end of Previous Year Right now:


I defined several dynamic date measures (Not sure if this is good practice or not since I'm totally new to this space):

Headcount

[Headcount]=DISTINCTCOUNT('Headcount Files'[EE_ID])

End of Quarter

[EOQ] =ENDOFQUARTER('Headcount Files'[Q_End_Date])

End of Previous Year

[EOPY]=Date(YEAR(Today())-1,12,31)

End of Previous Quarter

[EOPQ]=Calculate([EOQ],DateAdd('Headcount Files'[Q_End_Date],-1,QUARTER))

This seems to work for the Current Quarter Headcount:

CALCULATE(
    [Headcount],
    FILTER(
        ALL('Headcount Files'[Q_End_Date]),
        'Headcount Files'[Q_End_Date]=MAX('Headcount Files'[Q_End_Date])
    )
)

but when I try to use this for Previous Year End Headcount it doesn't work:
(THIS IS THE FORMULA THAT IS NOT WORKING)

CALCULATE(
    [Headcount],
    FILTER(
        ALL('Headcount Files'[Q_End_Date]),
        'Headcount Files'[Q_End_Date]=[EOPY])
    )
)

Can someone please help me resolve my issue and explain the error in my logic? Thanks for taking the time to review my inquiry.

 

[mrmmickle1]

After some trial and error this works:

PY Headcount:=CALCULATE(
    [Headcount],
    FILTER(
        ALL('Headcount Files'[Q_End_Date]),
        'Headcount Files'[Q_End_Date]=DATEVALUE([EOPY]))
    )