Count by Category

Lucy64

Remember with this setup your DATE table has only 1 Active Relationship with the PERSONAL table based on the HIRE_DATE!
Therefore when you filter/slice the data ONLY what matches your selection in the HIRE_DATE column will be considered!
So anytime you use the Year or Date Slicer or Timeline to limit the dates

it will filter your table ONLY considering what matches your selection in the HIRE_DATE and not the TERM_DATE!

However note that your Measures will produce the Correct Result because you use the USERELATIONSHIP function.

I created these Measures and got the same exact results as before:

Number Hired = COUNTA(PERSONAL[HIRE_DATE])

Number Fired =
CALCULATE (
    COUNTA ( PERSONAL[TERM_DATE] ),
    USERELATIONSHIP ( DATES[Date], PERSONAL[TERM_DATE] )
)

Balance = [Number Hired] - [Number Fired]

Number of Current Employess =
CALCULATE (
    [Balance],
    FILTER ( ALL ( DATES ), DATES[Date] <= MAX ( DATES[Date] ) )
)

Clear all slicers and timelines and look at your Data:

Then Select YEAR => 2016

Feb 2016 disappears in the second table (sorted by TERM_DATE) because it is filtered based on HIRE_DATE!

So when you filter remember you are filtering what matches in the HIRE_DATE not TERM_DATE!

However as you can see your Measures calculate the correct results.
So in the Matrix and the Chart the results are correct because we address this by using USERELATIONSHIP!

Hope this helps! :smileyhappy: