Forum Discussion

ojhassonu's avatar
ojhassonu
Frequent Visitor
5 years ago

Active Headcount at Year level showing wrong data

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"})
)

 

 

 

Need Help

2 Replies

  • TheoC's avatar
    TheoC
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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