cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rajveer1117
Helper IV
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
CNENFRNL
Super User
Super User

CNENFRNL_0-1645624610215.png

 

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

CNENFRNL_1-1645611422797.png


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!

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
Super User
Super User

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

SummarizedTable1.PNG

 

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

 

 

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!

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
Super User
Super User

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

DateDiff.PNG

 

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!

Hi moizsherwani ,



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

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors