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

[Anonymous]

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

[CNENFRNL]

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

[Anonymous]

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

[moizsherwani]

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

SummarizedTable = 
ADDCOLUMNS (
    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

[Anonymous]

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 = 
ADDCOLUMNS (
    SUMMARIZE (
        ZCV_ZPA_AD02_01,
        ZCV_ZPA_AD02_01[Employee],
       
        "MaxPosition", LASTNONBLANK ( ZCV_ZPA_AD02_01[Position], MAX ( ZCV_ZPA_AD02_01[Valid From] ) )
    ),
    "MinValidFrom",
        CALCULATE (
            MIN ( ZCV_ZPA_AD02_01[Valid From]),
            FILTER ( ZCV_ZPA_AD02_01, ZCV_ZPA_AD02_01[Position] = [MaxPosition] )
        ),
    "MaxValidTo",
        CALCULATE (
            MAX ( ZCV_ZPA_AD02_01[Valid To]),
            FILTER ( ZCV_ZPA_AD02_01, ZCV_ZPA_AD02_01[Position] = [MaxPosition])
        )
)

[moizsherwani]

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

[Anonymous]

Hi moizsherwani ,



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