Add another filter to a measue

[Anonymous]

Hi

I am doing some calculations of headcount by tenure and need help.

The below measure is one succesfully created to tell me what is the active start of month headcout by month.

Active SOM HC by Month = CALCULATE(COUNTROWS('common DimEmployee'),filter('common DimEmployee','common DimEmployee'[StartDate]<= STARTOFMONTH('common vwDimDate'[Date])&&'common DimEmployee'[End Date Adjusted]>=STARTOFMONTH('common vwDimDate'[Date])))

I am keeping this measure but want to create a new measure that adds another filter to above. I need the active start of month headcount by month for employees with less than 1 years tenure. It need to be a snapshot in time. So basically the 'common DimEmployee'[StartDate] field needs to be within a year of the 'common DimDate'[Date] I am dimensioning against.

I have tried various options previousyear function, datesinperiod function etc without any success.

[v-caliao-msft · Accepted solution]

@Anonymous,

You could add another filter to your measure.
Active SOM HC by Month =
var today = TODAY() 

return CALCULATE(COUNTROWS('common DimEmployee'),filter('common DimEmployee','common DimEmployee'[StartDate]<= STARTOFMONTH('common vwDimDate'[Date])&&'common DimEmployee'[End Date Adjusted]>=STARTOFMONTH('common vwDimDate'[Date])&&1*(today-'common DimEmployee'[StartDate])<365))

Regards,

Charlie Liao