Filtering a measure

[Anonymous]

Hi All,

I have a measure which calculates the 12 month rolling headcount from an employee file. Measure as below.

However, i need it to exclude certain "Contract Types" so i have a field called Contract Type, and i need it to count just Perm and Fix term and exclude Agency etc. How would i amend this measure to do that? Thanks in advance for any suggestions!

Rolling 12 month HC =

CALCULATE(

COUNT( 'HC'[Employee ID] ),

DATESINPERIOD( 'Date'[Date], MAX( 'Date'[Date] ), -12, MONTH ))

[Anonymous]

It didnt work with the "&& table[contract type] <> Agency line

But has worked with just the IN line. Thank you!!

[Samarth_18 · Accepted solution]

Hi @Anonymous ,

You can try like this:-

Rolling 12 month HC =
CALCULATE (
    COUNT ( 'HC'[Employee ID] ),
    DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -12, MONTH ),
    FILTER (
        'table',
        table[Contract Type]
            IN { "Perm", "Fix" }
            && table[Contract Type] <> Agency
    )
)

Thanks,

Samarth