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...
Need Help
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!