cancel
Showing results 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.

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

 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
5 REPLIES 5
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.

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.

Do you have any solution for this problem?

Community Support

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

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

Announcements

#### 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

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors