sum of "n/A"

[bestmbaman]

Hi,
This question was resolved but the answer can not be posted so I decided to delete the questions completely!

[bestmbaman · Accepted solution]

It is solved by myself!

[DataNinja777]

Hi @bestmbaman ,

It appears that your coutofemployees column is a numerical column, and you are trying to filter the precalculated employee number column.  Is this assumption correct?  However, if you have the data which has the information of

• Employee ID,
• Joining date,
• Leaving date

instead of filtering the precalculate numerical column with date field, you can perform much more flexible headcount analysis, using a disconnected calendar table and a dax formula like below:

Headcount =
SUMX (
    'Employee',
    IF (
        MAX ( 'Calendar'[Date] ) >= 'Employee'[Joining date]
            && MAX ( 'Calendar'[Date] ) <= 'Employee'[Leaving date],
        1,
        BLANK ()
    )
)

 I attach a relevant article which discusses this topic.  

https://p3adaptive.com/finding-the-magic-part-2-on-the-way-to-data-happiness/

Best regards,