Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

HR Dashboard Calculate turnovers and internal movements

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

 

DateIDDepartment  
2022-01-281000Sales  
2022-01-281001Sales  
2022-01-281002HR  
2022-01-281003HR  
2022-01-281004Digital  
2022-01-281005Digital  
2022-02-281000Sales  
2022-02-281001Sales  
2022-02-281002Sales  
2022-02-281003HR  
2022-02-281004Digital  
2022-02-281006Digital  
2022-03-281000Sales  
2022-03-281001Sales  
2022-03-281002Digital  
2022-03-281003HR  
2022-03-281004Digital   
2022-03-281006Digital  
     
     
     
5 REPLIES 5
v-yanjiang-msft
Community Support
Community Support

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.

vkalyjmsft_0-1649410759519.png

vkalyjmsft_1-1649410774017.png

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
Not applicable

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.

PatricL_0-1649420482374.png

Do you have any solution for this problem?

 

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
Memorable Member
Memorable Member

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

Anonymous
Not applicable

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 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.