Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Max(Date) returning dates earlier

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 D...
  • Anonymous's avatar
    Anonymous
    2 years ago

    123abc Thanks for your contribution on this tread.

    Hi Anonymous ,

    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

123abc's avatar
123abc
Icon for Community Champion rankCommunity Champion
2 years ago

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.