HR Dashboard Calculate turnovers and internal movements

[Anonymous]

I´m making a HR dashbord and want to calculate both internal and external movments.

My data is uppdated every months,  how can i make a messure that show me how many have left the company anda how many have change department. Also if I filter month by month and total if a filter total year. 

I also want to se by department if i Filter on it. 

See exampel data:

The date is from the every file that I get every month, The emplyee with red collor(1005) ended his empoyment that month. The orange one (1002) change department that month. Ande the green(1006) is a new hire.

Is it possible to do a messure with this? so I can see how many turnoves by department is a internal and how many have left the company+

Many thanks

Date	ID	Department
2022-01-28	1000	Sales
2022-01-28	1001	Sales
2022-01-28	1002	HR
2022-01-28	1003	HR
2022-01-28	1004	Digital
2022-01-28	1005	Digital
2022-02-28	1000	Sales
2022-02-28	1001	Sales
2022-02-28	1002	Sales
2022-02-28	1003	HR
2022-02-28	1004	Digital
2022-02-28	1006	Digital
2022-03-28	1000	Sales
2022-03-28	1001	Sales
2022-03-28	1002	Digital
2022-03-28	1003	HR
2022-03-28	1004	Digital
2022-03-28	1006	Digital

[v-yanjiang-msft]

Hi @Anonymous ,

According to your description, here's my solution.

Create two measures.

Count of change department =
CALCULATE (
    COUNT ( 'Table'[ID] ),
    FILTER (
        'Table',
        'Table'[Date] = SELECTEDVALUE ( 'Table'[Date] )
            && 'Table'[ID]
                IN SELECTCOLUMNS (
                    FILTER (
                        ALL ( 'Table' ),
                        MONTH ( 'Table'[Date] )
                            = MONTH ( SELECTEDVALUE ( 'Table'[Date] ) ) - 1
                    ),
                    "ID", 'Table'[ID]
                )
                    && 'Table'[Department]
                        <> MAXX (
                            FILTER (
                                ALL ( 'Table' ),
                                'Table'[ID] = EARLIER ( 'Table'[ID] )
                                    && MONTH ( 'Table'[Date] )
                                        = MONTH ( EARLIER ( 'Table'[Date] ) ) - 1
                            ),
                            'Table'[Department]
                        )
    )
)

Count of left =
CALCULATE (
    COUNT ( 'Table'[ID] ),
    FILTER (
        'Table',
        'Table'[Date] = SELECTEDVALUE ( 'Table'[Date] )
            && NOT (
                'Table'[ID]
                    IN SELECTCOLUMNS (
                        FILTER (
                            ALL ( 'Table' ),
                            MONTH ( 'Table'[Date] )
                                = MONTH ( SELECTEDVALUE ( 'Table'[Date] ) ) + 1
                        ),
                        "ID", 'Table'[ID]
                    )
            )
                && SELECTEDVALUE ( 'Table'[Date] ) <> MAXX ( ALL ( 'Table' ), 'Table'[Date] )
    )
)

Get the correct result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[Anonymous]

hi @v-yanjiang-msft thanks for the help! It´s alomst gives me what i´m searching for.

In janary 2022 i dont get the count of change of department and in december i get that al have left.

Do you have any solution for this problem?

[v-yanjiang-msft]

Hi @Anonymous ,

Does the formula work for sample in the original post but not work in your fact data? Actually, I'm not very clear about your problem without the sample data, would you please provide more information?

Best Regards,
Community Support Team _ kalyj

[davehus]

Hi @Anonymous , Have you got a status column that indicates the changes you are trying to measure?

[Anonymous]

HI @davehus 

No, only a column with a start date at the company.

nothing that shows ID has transfer inside the company. more than a new department name