Forum Discussion

netanel's avatar
netanel
Post Prodigy
4 years ago

HC ID Number count

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

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      netanel
      Post Prodigy
      4 years ago

      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!