COUNTROWS based off of most recent file date not wokring

[jlaroo33]

Hello! I am working on an employee headcount report, where my data builds each day with a new report run date. I am trying to capture the number of active Males and the number of active Females as of today. The measure I'm using to count the active employees is:

Active Employees NEW =

CALCULATE(

COUNTROWS('HeadCount_Term_Report NEW'),

LASTDATE('HeadCount_Term_Report NEW'[Report Run Date]))

The issue I'm having is that 2 employees had previously had the Gender "Not Specified" when they were first hired and the gender has since been updated, but those two records are still being counted in my totals on the visual. 

I hope this makes sense! Thank you in advance!

Jodi

[jlaroo33 · Accepted solution]

@tamerj1 , actually disregard that! I missed entering the actual employee IDs into "emp 1" and "emp 2". 

Thank you so much!

[tamerj1]

Hi @jlaroo33 

If these are the only 2 employees tha had the Gender "Not Specified" and there are no other record that has the Gender "Not Specified" then you may try

Active Employees NEW =
CALCULATE (
    COUNTROWS ( 'HeadCount_Term_Report NEW' ),
    LASTDATE ( 'HeadCount_Term_Report NEW'[Report Run Date] ),
    'HeadCount_Term_Report NEW'[Gender] <> "Not Specified"
)

In case you have records that have the gender "Not Specified" other than these 2 employees and you want them to be included with the count then you may try

Active Employees NEW =
VAR ExcludedRecords =
    CALCULATETABLE (
        'HeadCount_Term_Report NEW',
        'HeadCount_Term_Report NEW'[empID] IN { "emp1", "emp2" },
        'HeadCount_Term_Report NEW'[Gender] = "Not Specified"
    )
VAR LastDateTable =
    CALCULATETABLE (
        'HeadCount_Term_Report NEW',
        LASTDATE ( 'HeadCount_Term_Report NEW'[Report Run Date] )
    )
RETURN
    COUNTROWS ( EXCEPT ( LastDateTable, ExcludedRecords ) )

[jlaroo33]

Hi @tamerj1 ,

Thank you for looking into my issue. Unfortunately, the second formula that you provided (which is what I want, to include other "Not Specified" employees) did not see to work. Those two records are still being counted. 

Thank you, Jodi