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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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.

Anonymous's avatar
Anonymous
Not applicable
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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Wonderful! That seems to have resolved the issue. Thanks Anonymous !