count of active ppl

[n00ne]

Hi all,

Again I would like to ask you for your help.

I have one table with empid and rise date looking as follows:

EmpID RiseDate
1          01 January 2022
2          01 January 2022
3          01 February 2022
2          01 March 2022
3          01 March 2022
4          01 April 2022
5          01 April 2022

With this I would like to have distinct count of people for each year. So in Jan I would like to have as a result 2, in Feb 3, in March 3 again because these are same people based on empid getting rise.

And then I have second table telling me if some1 is no longer working for us:

EmpID TerminationDate
3          01 April 2022

So for the results for April I would like to have 4, because 2 new people got rise but one of the person who got rise before april in same year is no longer with us, so he/she should not be counted anymore.

The solution should work only for each year separately as mentioned before.

I would be much appreciated for any tips how to solve this.

Thank you very much in advance for your help.

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

I suggest having a dim-date table like below.

Distinct count in a year: =
COUNTROWS (
    CALCULATETABLE (
        SUMMARIZE ( Rise, Employee[EmpID] ),
        'Date'[Date] <= MAX ( 'Date'[Date] ),
        'Date'[Year] = MAX ( 'Date'[Year] )
    )
)
    - COUNTROWS (
        CALCULATETABLE (
            SUMMARIZE ( Termination, Employee[EmpID] ),
            'Date'[Date] <= MAX ( 'Date'[Date] ),
            'Date'[Year] = MAX ( 'Date'[Year] )
        )
    )

[n00ne]

Hi, Thank you for your respond.

The solution will not work in situation when in termination table will be more rows but they will not be present in rise table i.e.

EmpID TerminationDate
3          01 April 2022

6          01 April 2022

Solution should cross-check if person from termination was present in respective year in rise table and then subtrack it from result.

[Jihwan_Kim · Accepted solution]

Hi,

Thank you for your explanation.

Please check the below and the attached file if it suits your requirement.

Thank you.

Distinct count in a year: =
VAR _emplist =
    CALCULATETABLE (
        SUMMARIZE ( Rise, Employee[EmpID] ),
        'Date'[Date] <= MAX ( 'Date'[Date] ),
        'Date'[Year] = MAX ( 'Date'[Year] )
    )
RETURN
    COUNTROWS (
        CALCULATETABLE (
            SUMMARIZE ( Rise, Employee[EmpID] ),
            'Date'[Date] <= MAX ( 'Date'[Date] ),
            'Date'[Year] = MAX ( 'Date'[Year] )
        )
    )
        - COUNTROWS (
            CALCULATETABLE (
                FILTER (
                    SUMMARIZE ( Termination, Employee[EmpID] ),
                    Employee[EmpID] IN _emplist
                ),
                'Date'[Date] <= MAX ( 'Date'[Date] ),
                'Date'[Year] = MAX ( 'Date'[Year] )
            )
        )

[n00ne]

Thank you for your help.

Really appreciated.