Dax for cummulative count of staff headcount

[Osaz]

I have been trying to create a dax measure to dynamically calculate employee headcount every month based on existing staff, new staff and exited staff. HELP!!!

[v-jiascu-msft · Accepted solution]

Hi @Osaz,

All the staff IDs are unique. In other words, all the IDs are new ID. 

How to define the existing staff, new staff, and existed staff?

BTW, don't post private data or sensitive data here.

Best Regards,

Dale

[v-jiascu-msft]

Hi @Osaz,

Could you please mark the proper answer as a solution?

Best Regards,
Dale

[v-jiascu-msft]

Hi @Osaz,

Did you have any issues? What can we help you? It would be great to post a sample of your data. 

Best Regards,

Dale

[Osaz]

Hi Dale:

https://1drv.ms/u/s!AkdGvWz6zLNXg79ihm8T3r8gr3HJYA

Thats a link to the file.

Osaz

[v-jiascu-msft · Accepted solution]

Hi @Osaz,

All the staff IDs are unique. In other words, all the IDs are new ID. 

How to define the existing staff, new staff, and existed staff?

BTW, don't post private data or sensitive data here.

Best Regards,

Dale

[Osaz]

all the id's are unique, however, we have a hire date and an exit date. How do I marry the two dates in order to establish employee headcount on any given date.

Should I delet the link, though

Osaz

[v-jiascu-msft · Accepted solution]

Hi Osaz,

It's for your privacy and data security. It's up to you to keep or delete the link. 

Maybe my guess is correct. Please check out the demo in the Private Message.

New Staff Count = COUNT(MasterList[Staff ID])

Existed Staff =
CALCULATE (
    COUNT ( MasterList[Staff ID] ),
    FILTER (
        ALL ( 'Invoked Function'[Date] ),
        'Invoked Function'[Date] < MIN ( 'Invoked Function'[Date] )
    )
)

Existing Staff =
CALCULATE (
    COUNT ( MasterList[Staff ID] ),
    FILTER (
        ALL ( MasterList ),
        MasterList[DOE] < MIN ( 'Invoked Function'[Date] )
            && (
                MasterList[PRExit Date] > MAX ( 'Invoked Function'[Date] )
                    || ISBLANK ( MasterList[PRExit Date] )
            )
    )
)

Best Regards,

Dale

[Osaz]

thank you. yeah, will make sure to  review before posting.