HC ID Number count

[netanel]

Hey All!

I have a Person Number column
And I want to check from month to month whether a person has left work or joined
I thought of the following logic but it does not work for me
Maybe you Can help?
1. If Person Number existed in the previous month and now not then "left"
2. If Person Number did not exist in the previous month but exists now then "Join"

And put it in a separate column where you will have "left" or "join"

Sounds simple but it does not work for me ...

Link to file:

https://1drv.ms/f/s!AonyYI-TdspHgUhwngZ6OKvpnSsc

[Anonymous]

Hi @netanel ,

I think you can try code as below to achieve your goal.

Count of Join =
VAR _SUMMAZIRE =
    SUMMARIZE (
        ALL ( 'Data HC' ),
        'Data HC'[Person Number],
        "MaxDate", CALCULATE ( MAX ( 'Data HC'[Date] ), ALL ( 'Data HC' ) ),
        "CountofDate", CALCULATE ( COUNT ( 'Data HC'[Date] ) ),
        "Personal MaxDate", CALCULATE ( MAX ( 'Data HC'[Date] ) )
    )
RETURN
    COUNTAX (
        FILTER ( _SUMMAZIRE, [CountofDate] = 1 && [Personal MaxDate] = [MaxDate] ),
        [Person Number]
    )

Count of left =
VAR _SUMMAZIRE =
    SUMMARIZE (
        ALL ( 'Data HC' ),
        'Data HC'[Person Number],
        "MaxDate", CALCULATE ( MAX ( 'Data HC'[Date] ), ALL ( 'Data HC' ) ),
        "CountofDate", CALCULATE ( COUNT ( 'Data HC'[Date] ) ),
        "Personal MaxDate", CALCULATE ( MAX ( 'Data HC'[Date] ) )
    )
RETURN
    COUNTAX (
        FILTER ( _SUMMAZIRE, [Personal MaxDate] = EOMONTH ( [MaxDate], -1 ) ),
        [Person Number]
    )

Result is as below.

Best Regards,
Rico Zhou

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

[netanel]

Thanks @Anonymous 

Thanks for the help
But something is wrong
I do not know to point out exactly what the problem is
I also do not close well to numbers
And with you (your dates don't work either)

The logic is simple
Whoever was a month ago and today is not then he left
Anyone who was not a month ago and today is yes then joined

Attach again the file I arranged it a little more hope you understand

https://1drv.ms/f/s!AonyYI-TdspHgUhwngZ6OKvpnSsc

Thanks!