Forum Discussion

ehmacc's avatar
ehmacc
Frequent Visitor
6 years ago

Formula Help- Distinct count related to multiple criteria

Hi everyone-  I am trying to solve for an issue and have worked at it for way too long, need to finally ask for help. I am working on turnover metrics which requires Average Active employees for a d...
Need Help
dax's avatar
dax
Icon for Community Support rankCommunity Support
6 years ago

Hi ehmacc,

Did you mean you want to get interactive based on date range slicer? If so, you could try below measure

 

Measure =
VAR maxd =
    CALCULATE ( MAX ( 'Term Date'[Date] ), ALLSELECTED ( 'Term Date'[Date] ) )
VAR mind =
    CALCULATE ( MIN ( 'Term Date'[Date] ), ALLSELECTED ( 'Term Date'[Date] ) )
RETURN
    CALCULATE (
        COUNT ( Employment[Department] ),
        FILTER (
            Employment,
            Employment[Termination Date] < maxd
                && Employment[Termination Date] > mind
                || Employment[Termination Date] = BLANK ()
        )
    )

 

 If this is not what you want , please correct me and inform em your expected output.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

ehmacc's avatar
ehmacc
Frequent Visitor
6 years ago

dax

That equation only returns the count of employees with termination dates in that range, it isn't picking up employees without a termination date, which I need included.

 

I also had to change the filter to include Hire date as the first piece- it should filter for employees who were Hired before the last day of the date range who either had a term date greater than the first day of the date range or no term date. But this has not fixed the count problem- it is still only counting if they have a termination date. 

Measure =
VAR maxd =
    CALCULATE ( MAX ( 'Term Date'[Date] ), ALLSELECTED ( 'Term Date'[Date] ) )
VAR mind =
    CALCULATE ( MIN ( 'Term Date'[Date] ), ALLSELECTED ( 'Term Date'[Date] ) )
RETURN
    CALCULATE (
        COUNT ( Employment[Department] ),
        FILTER (
            Employment,
            Employment[Hire Date] < maxd
                && Employment[Termination Date] > mind
                || Employment[Termination Date] = BLANK ()
        )
    )