cancel
Showing results for
Did you mean:
Helper IV

## How to get Last Position change of the employee based on the given data

Need help in solving the below problem:

I need to calcuate the tenure based on the last position change:

``````Emp	Position	valid From	Valid To
9999	45002871	20131115	20131231
9999	45002871	20140101	20140131
9999	45002871	20140201	20140228
9999	45002871	20140301	20140601
9999	45002871	20140602	20140810
9999	45002871	20140811	20140909
9999	45002871	20140910	20141231
9999	45002871	20150101	20150228
9999	45002871	20150301	20150920
9999	50029706	20150921	20151018
9999	50029706	20151019	20151231
9999	50029706	20160101	20160229
9999	50029706	20160301	20160329
9999	50029706	20160330	20170331
9999	50029706	20170401	20180331
9999	50029706	20180401	20190331
9999	50029706	20190401	20200331
9999	50029706	20200401	99991231
1000	50015729	20180301	20180430
1000	50015729	20180501	20180507
1000	50015729	20180508	20180615
1000	50015729	20180616	20180731
1000	50015729	20180801	20181026
1000	50015729	20181027	20181031
1000	50045362	20181101	20181231
1000	50045362	20190101	20190127
1000	50045362	20190128	20190331
1000	50018458	20190401	20191003
1000	50018458	20191004	20191008
1000	50018458	20191009	20191231
1000	50018458	20200101	99991231

Output
emp	position	Valid from	Valid To	No f days
9999	50029706	9/21/2015	3/31/2020	4.528767123
1000	50018458	4/1/2019	12/31/2019	0.750684932``````

6 REPLIES 6
Super User

Excel worksheet formula is powerful enough to solve such a simple, single-table lookup question.

 Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is beyond their comprehension! DAX is simple, but NOT EASY!
Helper IV

Hi,

I got a scnerio where we have position change in between and i need to pick the lastest change date. Here is an example.

``````Employee	Valid From	Valid To	Position
1000	1/1/2016 0:00	12/17/2017 0:00	50015729
1000	12/18/2017 0:00	2/28/2018 0:00	50018458
1000	3/1/2018 0:00	4/30/2018 0:00	50015729
1000	5/8/2018 0:00	6/15/2018 0:00	50015729
1000	6/16/2018 0:00	7/31/2018 0:00	50015729
1000	8/1/2018 0:00	10/26/2018 0:00	50015729
1000	10/27/2018 0:00	10/31/2018 0:00	50015729
1000	11/1/2018 0:00	12/31/2018 0:00	50045362
1000	1/28/2019 0:00	3/31/2019 0:00	50045362
1000	4/1/2019 0:00	10/3/2019 0:00	50018458
1000	10/9/2019 0:00	10/9/2019 0:00	50018458
1000	1/1/2020 0:00	12/31/9999 0:00	50018458

O/P I am getting

Employee	Start of Prev Position	Valid From	position	Valid To	Tenure
1000	12/18/2017 0:00	10/9/2019 0:00	50018458	10/9/2019 0:00	2.04

Actual O/p Should be
Employee	Start of Prev Position	Valid From	position	Valid To	Tenure
1000	4/1/2019 0:00	10/9/2019 0:00	50018458	10/9/2019 0:00	0.52``````
Super User

And here is the better solution. After creating the summarized table I just created a calculated column as below

``````SummarizedTable =
SUMMARIZE (
EmpTable,
EmpTable[Emp],
"MaxPosition", LASTNONBLANK ( EmpTable[Position], MAX ( EmpTable[valid From] ) )
),
"MinValidFrom",
CALCULATE (
MIN ( EmpTable[valid From] ),
FILTER ( EmpTable, EmpTable[Position] = [MaxPosition] )
),
"MaxValidTo",
CALCULATE (
MAX ( EmpTable[valid to] ),
FILTER ( EmpTable, EmpTable[Position] = [MaxPosition] )
)
)``````

``````DateDiff =
DATEDIFF ( SummarizedTable[MinValidFrom], SummarizedTable[MaxValidTo], DAY ) / 365``````

Thanks,

Moiz
Was I able to answer your question? Mark my post as a solution to help others. Kudos if you liked the solution.
Proud to be a Super User!
Helper IV
``````Hi,

I followed the summarize table option and i m not getting the right result.
Employee	Valid From	Valid To	Position	Organizational Unit
1000	1/1/2016 0:00	12/17/2017 0:00	50015729	50032413
1000	12/18/2017 0:00	2/28/2018 0:00	50018458	50044101
1000	3/1/2018 0:00	4/30/2018 0:00	50015729	50032413
1000	5/8/2018 0:00	6/15/2018 0:00	50015729	50032413
1000	6/16/2018 0:00	7/31/2018 0:00	50015729	50032413
1000	8/1/2018 0:00	10/26/2018 0:00	50015729	50032413
1000	10/27/2018 0:00	10/31/2018 0:00	50015729	50032413
1000	11/1/2018 0:00	12/31/2018 0:00	50045362	50043762
1000	1/28/2019 0:00	3/31/2019 0:00	50045362	50043762
1000	4/1/2019 0:00	10/3/2019 0:00	50018458	50044101
1000	10/9/2019 0:00	12/31/2019 0:00	50018458	50044101
1000	1/1/2020 0:00	12/31/9999 0:00	50018458	50044101

o/p
Employee	Position	MaxValid To	Minvalid Form
1000	50045362	Sun Mar 31 2019 00:00:00 	Thu Nov 01 2018 00:00:00

SummarizedTable =
SUMMARIZE (

),
"MinValidFrom",
CALCULATE (
),
"MaxValidTo",
CALCULATE (
)
)``````
Super User

Pretty sure there is a more efficient way but it gets the job done for now

``````DateDiff_MinValidFrom_MaxValidTo =
VAR CurrentEmp =
SELECTEDVALUE ( EmpTable[Emp] )
VAR LastValidFrom =
CALCULATE (
MAX ( EmpTable[valid From] ),
ALLEXCEPT ( EmpTable, EmpTable[Emp] )
)
VAR MaxPosition =
LOOKUPVALUE (
EmpTable[Position],
EmpTable[Emp], CurrentEmp,
EmpTable[valid From], LastValidFrom
)
VAR MinValidFrom =
CALCULATE (
MIN ( EmpTable[valid From] ),
FILTER (
ALLEXCEPT ( EmpTable, EmpTable[Emp] ),
EmpTable[Position] = MaxPosition
)
)
VAR MaxValidTo =
CALCULATE (
MAX ( EmpTable[valid To] ),
FILTER (
ALLEXCEPT ( EmpTable, EmpTable[Emp] ),
EmpTable[Position] = MaxPosition
)
)
RETURN
DATEDIFF ( MinValidFrom, MaxValidTo, DAY ) / 365``````
Thanks,

Moiz
Was I able to answer your question? Mark my post as a solution to help others. Kudos if you liked the solution.
Proud to be a Super User!
Helper IV
``````Hi moizsherwani ,

I don't want to select employee. Is it possible with selecting emp.``````

Announcements