How do I add a current year filter with text filter

[tscongack]

Hi there,

I'm attempting to add multiple filters to a column, along with one that takes the year out of a date field and returns values that equal the current year.

Each text filter I'd like it to act as "or" or return if any of the conditions are met

Current in 2017 = CALCULATE(COUNTROWS(EmployeeRecord),EmployeeRecord[Status] = "Active", EmployeeRecord[Status] = "On Leave - Parental", EmployeeRecord[Status] = "On Leave - Unpaid", EmployeeRecord[Status] = "On Leave - Long Term Disability"), (Year(EmployeeRecord[Termination Date]) = (Year[Today])

I'm not sure how to make a filter that selects entries that contain "On Leave"  - is there a way to do this as well?

Thanks in advance for your help!

[v-jiascu-msft]

Hi @tscongack,

Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

Best Regards!
Dale

[v-jiascu-msft]

Hi @tscongack,

1. You want to add a year condition. Maybe you can try it like this:

Current in 2017 =
CALCULATE (
    COUNTROWS ( EmployeeRecord ),
    FILTER (
        EmployeeRecord,
        EmployeeRecord[Status]
            IN {
                "Active",
                "On Leave - Parental",
                "On Leave - Unpaid",
                "On Leave - Long Term Disability" }
            || YEAR ( EmployeeRecord[Termination Date] ) = YEAR ( TODAY () )
    )
)

BTW, the conditions organized in your format will be applied with "AND".

2. Filter conditions of containing "On Leave", you can try this one. Refer to SEARCH.

Current in 2017 =
CALCULATE (
    COUNTROWS ( EmployeeRecord ),
    FILTER ( EmployeeRecord, SEARCH ( "On Leave", EmployeeRecord[Status], 1, 0 ) )
)

Best Regards!

Dale

[Ashish_Mathur]

Hi,

I'd suggest that you create a calendar table wih running dates there.  In that calendar table, extract the year from the Date column using =YEAR(Calendar[Date]).  Create a relationship from the Termination Date of the EmployeeRecord table to the Date column of the calendar table.  Drag year from the calendar table to yuor visual.  Now try this formula

=CALCULATE(COUNTROWS(EmployeeRecord),FILTER(EmployeeRecord,

EmployeeRecord[Status] = "Active"||EmployeeRecord[Status] = "On Leave - Parental"||EmployeeRecord[Status] = "On Leave - Unpaid"||EmployeeRecord[Status] = "On Leave - Long Term Disability"))

Hope this helps.