Calculate "age" of employee at a given month.

[Anonymous]

Hello ,

I am looking to create a visualization for the age of employees for a given month. I have the start date of an employee. If the employee is promoted internally or has quit, there is an end date. If the employee joins in January, then on Feb, his age is 1 month. March, his age would be 2 months. If he quits in March or gets an internal promotion, then the age won't be calculated after that. 

This is the data I have :

Can someone help me with this please?

This is the visualization I am looking for.

[LivioLanzo · Accepted solution]

hey @Anonymous

download the power bi file Here: https://1drv.ms/u/s!AiiWkkwHZChHjxhUkuLvcCyKZMY_

[LivioLanzo · Accepted solution]

hey @Anonymous

download the power bi file Here: https://1drv.ms/u/s!AiiWkkwHZChHjxhUkuLvcCyKZMY_

[Anonymous]

Thank you so much! this works perfectly!!

[LivioLanzo · Accepted solution]

Hi @Anonymous

thank you for the feedback. Could you kindly mark my post as an accepted solution?

thank you

[Anonymous]

Here you go! 

Name of Employee	Date of Join	Date of Exit/Internal Promotion	Department	Current Department
a	1-Jan-10		AMG	AMG
b	1-Jan-12		AMG	AMG
c	1-Jan-16		AMG	AMG
d	1-Jan-16		AMG	AMG
e	1-Jan-17	21-May-18	AMG	CSP
f	1-Jan-17	13-Jul-18	AMG	exit
g	1-Jan-17	1-Apr-18	AMG	CSP
h	1-Mar-17	1-Apr-18	Validation Services	CSP
i	1-Jan-17	13-Jul-18	AMG	exit
j	1-Aug-17	18-Apr-18	AMG	Lead
k	1-Aug-17	18-Mar-18	AMG	Lead
l	1-Aug-17	1-Apr-18	AMG	CSP
m	1-Aug-17	18-Jun-18	AMG	exit
n	17-Dec-17		AMG	AMG
o	17-Dec-17		AMG	AMG
p	18-Feb-18		Validation Services	Lead
q	18-Feb-18	28-Feb-18	AMG	exit
r	18-Feb-18	18-Apr-18	AMG	exit

[PattemManohar]

@Anonymous Could you please post the sample data (able to copy) which will be helpful to resolve the issue quicker. Anyway, thanks for the screenshots.