Max(Date) returning dates earlier

Thank you for the clarification. If you want the default behavior to always show the status as at the maximum date of the whole table while still allowing Secondment to appear if relevant, you can modify the measure accordingly. You can achieve this by creating a calculated column to determine the latest employment status for each employee and then filter based on that column. Here's how you can do it:

1. Create a Calculated Column: First, create a calculated column that determines the latest employment status for each employee. This column will be used for filtering purposes.

Latest_Employment_Status =
VAR CurrentDate = MAX('FACT Workforce Data'[Report Date])
RETURN
CALCULATE(
MAX('FACT Workforce Data'[Employment Status]),
FILTER(
'FACT Workforce Data',
'FACT Workforce Data'[Employee ID] = EARLIER('FACT Workforce Data'[Employee ID]) &&
'FACT Workforce Data'[Report Date] <= CurrentDate
)
)

1. Modify the Headcount Measure: Adjust the headcount measure to filter based on this calculated column instead of the 'Active Status'.

Headcount =
CALCULATE (
DISTINCTCOUNT ('FACT Workforce Data'[Employee ID]),
'FACT Workforce Data'[Latest_Employment_Status] <> BLANK(),
'FACT Workforce Data'[Latest_Employment_Status] = "Active"
)

With this setup, the 'Latest_Employment_Status' column will always show the latest employment status for each employee up to the maximum date in the dataset. The headcount measure then filters based on this column, ensuring that only active employees are counted based on their latest employment status as of the maximum date. If Secondment is relevant for that date, it will appear accordingly in the 'Latest_Employment_Status' column.