Max(Date) returning dates earlier

[ForumDT]

hello! I am trying to calculate Employee data as at a point in time.

My Employee data table has report run date and the active employees at each report date. For example

Employee ID	Report Date	Employment Status	Active Status
1001	31/1/2024	Full Time	Active
1002	31/1/2024	Part Time	Active
1001	29/2/2024	Full Time	Terminated
1002	29/2/2024	Part Time	Active

My headcount measure for this is

Headcount = Calculate (
COUNTROWS('FACT Workforce Data'),
('FACT Workforce Data'[Report Date] = MAX('FACT Workforce Data'[Report Date]) && 'FACT Workforce Data'[Active Status] = "active"))

However at some point in the past a currently active employee was classified under an Employment Status called 'Secondment'. When I try to view the Employment Status of current employees, this classification appears. 

i can see that it is getting the max date that this classification appeared (31/12/2023) rather than the max date in the colum Report Date

Is there any way i can return only the values for the Max Date 29/02/2024? I have tried the below as well but it still returns the same result 

Headcount = 

 Var MaxReportDate = MAX('FACT Workforce Data'[Report Date])

 RETURN

 Calculate(

    DISTINCTCOUNT('FACT Workforce Data'[Employee ID]),

    ('FACT Workforce Data'[Report Date] = MaxReportDate && 'FACT Workforce Data'[Active Status] = "active"))

Any help would be greatly appreciated! thanks!

[ForumDT]

Thank you, however i'm trying to calculate the headcount and categories 'as at'. So i still want Secondment to appear if i am filtering by a date where it is relevant.

It should show up if i am reviewing employee data on or before 31/12/2023 but it shouldnt show up if i am reviewing any dates after this.

Obviously i could achieve this with a simple slicer to select dates, however i want the default behaviour to always show the status as at the Max date of the whole table.

[123abc]

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.

[ForumDT]

Thank you for taking the time to respond. 
I have already explored this with AI assistance to no avail but this probably needs a human review of the image attached and expected outcomes. 

I already have a solution as per your latest response however I need it calculated as a measure rather than a static filter.

[v-yiruan-msft · Accepted solution]

@123abc Thanks for your contribution on this tread.

Hi @ForumDT ,

@123abc provided a solution by creating a calculated column. You can make a little adjustment on the formula of your measure as below to get the expected result.

Headcount =
VAR MaxReportDate =
    CALCULATE (
        MAX ( 'FACT Workforce Data'[Report Date] ),
        ALLSELECTED ( 'FACT Workforce Data' )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'FACT Workforce Data'[Employee ID] ),
        FILTER (
            'FACT Workforce Data',
            'FACT Workforce Data'[Report Date] = MaxReportDate
                && 'FACT Workforce Data'[Active Status] = "Active"
        )
    )

Best Regards

[ForumDT]

Wonderful! That seems to have resolved the issue. Thanks @v-yiruan-msft !

[123abc]

To address this issue, you need to ensure that you're filtering out the 'Secondment' classification when determining the maximum report date. One way to achieve this is by filtering out rows with 'Employment Status' as 'Secondment' before calculating the maximum date.

Here's how you can modify your DAX expression to achieve this:

Headcount =
VAR MaxReportDate = CALCULATE(MAX('FACT Workforce Data'[Report Date]), 'FACT Workforce Data'[Employment Status] <> "Secondment")
RETURN
CALCULATE(
DISTINCTCOUNT('FACT Workforce Data'[Employee ID]),
'FACT Workforce Data'[Report Date] = MaxReportDate,
'FACT Workforce Data'[Active Status] = "Active"
)

1. We use CALCULATE instead of VAR to calculate the maximum report date.
2. We add a filter condition 'FACT Workforce Data'[Employment Status] <> "Secondment" to ensure that the 'Secondment' rows are excluded from consideration when calculating the maximum report date.
3. We use the calculated MaxReportDate to filter the rows in the subsequent CALCULATE function to count the distinct active employees at that maximum report date.

This should give you the headcount of active employees as of the maximum report date excluding the 'Secondment' classification.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.