Active Headcount at Year level showing wrong data

[ojhassonu]

Dear All,

I created a measure to calculate active headcount based on the time. When I selected year (for ex- 2020). It is showing correct data at month level, however when rolling up to Year, it is showing some other figures, as below screenshot. You can see Total head  count for 2020 in first and last row: 1691, 2683, 1145 , 5519(wrong). Which is wrong as we can see in Dec 2020 we have headcount is 1640, 2498,1129, 5267(correct).

Can someone help me to fix this issue and why it is showing wrong figures at year level.

Headcount =

CALCULATE (

// COUNT ( 'Fact - Emp'[EMPLOYEE_NUMBER] )

COUNTROWS('Fact - Emp') ,

FILTER (

VALUES ( 'Fact - Emp'[HIRE_DATE] ),

'Fact - Emp'[HIRE_DATE] <= MAX ( 'Dim - Date'[Date] )

),

FILTER (

VALUES ( 'Fact - Emp'[TERMINATION_DATE] ),

OR (

'Fact - Emp'[TERMINATION_DATE] >= MIN ( 'Dim - Date'[Date] ),

ISBLANK ( 'Fact - Emp'[TERMINATION_DATE] ) ) ),

FILTER('Fact - Emp','Fact - Emp'[COMPANY] IN {"Dell","ORACLE","IBM"})

)

[Anonymous]

Hi @ojhassonu ,

Remember that the total of the visual is not simply the sum of the column, it is calculated through the DAX formula as well.

Please show some sample data so that we could check the formula for you.

Best Regards,

Jay

[TheoC]

@ojhassonu it may be due to the filter being applied relating to "

"VALUES ( 'Fact - Emp'[TERMINATION_DATE] ), OR" in that it could be adding back the terminated employees. On phone so cannot test my thoughts but if the post isn't resolved by morning AEST, I will.get you a solution. 

Best of luck until then.

Theo