Need Help with Dax

[Anonymous]

I Calculated Attrition percentage using Dax mentioned below with Year filter with single select

Attrn % =

VAR _selval = SELECTEDVALUE(Dim_Date[Year])

Var _lastyear = Calculate(COUNT(EmployeeMaster[Employee ID]), Dim_Date[Year]<= _selval-1)

Var _thisyear = Calculate(COUNT(EmployeeMaster[Employee ID]), Dim_Date[Year]<= _selval)

Var _leavers = CALCULATE(COUNT(EmployeeMaster[Employee ID]),

USERELATIONSHIP(Dim_Date[Date],EmployeeMaster[last working day]),

Dim_Date[Year]=_selval)

Var _ForAttr = DIVIDE(_lastyear+_thisyear, 2,0)

return

DIVIDE(_leavers, _ForAttr, 0)

So, When user selects two years in Slicer, I want to find Attrition percent for Min Selected Year and Max Selected Year.

I am Using this Dax which the result is returning as 0.

Attrn % Max Year =

VAR _selval = CALCULATE(SELECTEDVALUE(Dim_Date[Year]), FILTER(Dim_Date, Dim_Date[Year] = MAX(Dim_Date[Year])))

Var _lastyear = Calculate(COUNT(EmployeeMaster[Employee ID]), Dim_Date[Year]<= _selval-1)

Var _thisyear = Calculate(COUNT(EmployeeMaster[Employee ID]), Dim_Date[Year]<= _selval)

Var _leavers = CALCULATE(COUNT(EmployeeMaster[Employee ID]),

USERELATIONSHIP(Dim_Date[Date],EmployeeMaster[last working day]),

FILTER(Dim_Date, Dim_Date[Year]=MAX(Dim_Date[Year])))

Var _ForAttr = DIVIDE(_lastyear+_thisyear, 2,0)

return

DIVIDE(_leavers, _ForAttr,0)
Please help

[tamerj1 · Accepted solution]

Hi @Anonymous 
Please try this

Attrn % Max Year =
VAR _selval =
    MAX ( Dim_Date[Year] )
VAR _lastyear =
    CALCULATE (
        COUNT ( EmployeeMaster[Employee ID] ),
        Dim_Date[Year] <= _selval - 1
    )
VAR _thisyear =
    CALCULATE ( COUNT ( EmployeeMaster[Employee ID] ), Dim_Date[Year] <= _selval )
VAR _leavers =
    CALCULATE (
        COUNT ( EmployeeMaster[Employee ID] ),
        USERELATIONSHIP ( Dim_Date[Date], EmployeeMaster[last working day] ),
        Dim_Date[Year] = _selval
    )
VAR _ForAttr =
    DIVIDE ( _lastyear + _thisyear, 2, 0 )
RETURN
    DIVIDE ( _leavers, _ForAttr, 0 )

Attrn % Min Year =
VAR _selval =
    MIN ( Dim_Date[Year] )
VAR _lastyear =
    CALCULATE (
        COUNT ( EmployeeMaster[Employee ID] ),
        Dim_Date[Year] <= _selval - 1
    )
VAR _thisyear =
    CALCULATE ( COUNT ( EmployeeMaster[Employee ID] ), Dim_Date[Year] <= _selval )
VAR _leavers =
    CALCULATE (
        COUNT ( EmployeeMaster[Employee ID] ),
        USERELATIONSHIP ( Dim_Date[Date], EmployeeMaster[last working day] ),
        Dim_Date[Year] = _selval
    )
VAR _ForAttr =
    DIVIDE ( _lastyear + _thisyear, 2, 0 )
RETURN
    DIVIDE ( _leavers, _ForAttr, 0 )

[Anonymous]

@tamerj1 It Works. Thank you