Calculate Days in Role/Project

[A_Ch]

Hi All,

I was in need of some help in calculating the number of days between events. I am trying to calculate how long employees were in certain roles or on certain projects. Below is how my data table is modeled.

Date	Employee ID	Role/Project	Event	Days Employed
1/1/2018	01	New Hire	New Hire	1
1/2/2018	01	New Hire		2
1/3/2018	01	New Hire		3
1/4/2018	01	Intern		4
1/5/2018	01	Intern		5
1/6/2018	01	Intern		6
1/7/2018	01	Intern		7
1/8/2018	01	Level 1	Role Change	8
1/9/2018	01	Level 1		9
1/10/2018	01	Level 1		10
1/11/2018	01	Level 1		11
1/12/2018	01	Level 1		12
1/13/2018	01	Level 1		13
1/14/2018	01	Level 1		14
1/15/2018	01	Level 2	Role Change	15
1/16/2018	01	Level 2		16
1/17/2018	01	Level 2		17

In my model, New Hire and Intern are considered as the same position essentially.

Any help is greatly appreciated.

[v-cherch-msft]

Hi @A_Ch

You may refer to below dax to create calculated columns.

Role/Project 2 =
IF ( Table3[Role/Project] = "Intern", "New Hire", Table3[Role/Project] )

Long =
COUNTROWS (
    FILTER ( Table3, Table3[Role/Project 2] = EARLIER ( Table3[Role/Project 2] ) )
)

Regards,

Cherie

[Greg_Deckler]

I'm guessing a column and use of EARLIER. See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...