Old Job Title and New Job Title Columns

[yaman123]

Hi, 

I am looking to create two columns - Old Job Title and New Job Title. 

My table has the following columns - 

Emp No, Name, Job Title, Valid From, Valid To. 

The Valid From and Valid To are dates showing the start and end date of the job. 

I am looking to display my data as below: 

Emp No, Name, Old Job Title, New Job Title, Valid From 

[amitchandak]

@yaman123 , Try new columns like

Old job Title =
var _max = maxx(filter(Table, Table[Emp No] = earlier(Table[Emp No]) && Table[Valid From] < earlier(Table[Valid From])), Table[Valid From])
return
maxx(filter(Table, Table[Emp No] = earlier(Table[Emp No]) && Table[Valid From] =_max), Table[Job Title])

Next job Title =
var _max = Minx(filter(Table, Table[Emp No] = earlier(Table[Emp No]) && Table[Valid From] > earlier(Table[Valid From])), Table[Valid From])
return
maxx(filter(Table, Table[Emp No] = earlier(Table[Emp No]) && Table[Valid From] =_max), Table[Job Title])

[yaman123]

Hi @amitchandak 

I am looking to display the old job and new job titles on one line. The query you have provided is giving me two or more rows per person. Can this be done to show in the one line only? 

[v-rzhou-msft]

Hi @yaman123 ,

According to your statement, I think you have old Jobs Titles and new Job Titles to same [Emp No] in your original table. We can determind whether the Job Title is new or old by Vaild From Date. Currently your requirement, is to show old Jobs Titles and new Job Titles to same [Emp No]  in one line.

I think your data will display like as below.

Emp No, Name, Old Job Title, New Job Title, Valid From to Old Job Title, Valid From to New Job Title

My Sample table is as below.

Try this code to create a calcualted table.

Table 2 = 
SUMMARIZE (
    'Table',
    'Table'[Emp No],
    'Table'[Name],
    "Valid From to Old Job Title", CALCULATE ( MIN ( 'Table'[Valid From] ) ),
    "Old Job Title",
        CALCULATE (
            MAX ( 'Table'[Job Title] ),
            FILTER ( 'Table', 'Table'[Valid From] = MIN ( 'Table'[Valid From] ) )
        ),
    "Valid From to New Job Title", CALCULATE ( MAX ( 'Table'[Valid From] ) ),
    "New Job Title",
        CALCULATE (
            MAX ( 'Table'[Job Title] ),
            FILTER ( 'Table', 'Table'[Valid From] = MAX ( 'Table'[Valid From] ) )
        )
)

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[yaman123]

Hi @v-rzhou-msft 

The query above is giviing me results for all employees even though they have not changed job titles. E.g Employee A is showing New Job Title as CEO and Old Job Title as CEO. 

Is there a way to only show employees who have had a change in the job title? 

[v-rzhou-msft]

Hi @yaman123 ,

According to your statement, I think you have situations that some names' Old Job Title and New Job Title are the same.

I update my sample, we can see that, User C has same Old Job Title and New Job Title.

I add a filter in my code that I distinct count the Job Title for each Name and we will only summarize Names whose Job Title counts is larger than 1.

Table 2 =
SUMMARIZE (
    FILTER (
        'Table',
        VAR _Count =
            CALCULATE (
                DISTINCTCOUNT ( 'Table'[Job Title] ),
                FILTER ( 'Table', 'Table'[Name] = EARLIER ( 'Table'[Name] ) )
            )
        RETURN
            _Count > 1
    ),
    'Table'[Emp No],
    'Table'[Name],
    "Valid From to Old Job Title", CALCULATE ( MIN ( 'Table'[Valid From] ) ),
    "Old Job Title",
        CALCULATE (
            MAX ( 'Table'[Job Title] ),
            FILTER ( 'Table', 'Table'[Valid From] = MIN ( 'Table'[Valid From] ) )
        ),
    "Valid From to New Job Title", CALCULATE ( MAX ( 'Table'[Valid From] ) ),
    "New Job Title",
        CALCULATE (
            MAX ( 'Table'[Job Title] ),
            FILTER ( 'Table', 'Table'[Valid From] = MAX ( 'Table'[Valid From] ) )
        )
)

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[yaman123]

Hi @v-rzhou-msft 

The amended query is not giving me the correct results unfortunately. 

I have the below sample dataset: 

Emp No	Name	Job Title	Valid From	Valid To
10001	A	Whey Technician	15/01/2020	02/08/2020
10001	A	Process Technician	03/08/2020	28/02/2021
10001	A	Cheese Technician	01/03/2021	31/10/2021
10001	A	Shift Manager	01/11/2021	28/02/2022
10001	A	Cheese Technician	01/03/2022	31/12/9999
10002	B	Engineering Manager	19/05/2020	31/10/2021
10002	B	Production Manager	01/11/2021	31/12/2021
10002	B	Engineering Manager	01/01/2022	31/12/9999
10003	C	Data Analyst	03/12/2018	31/12/9999

Expected Output:

Emp No	Name	Old Job Title	New Job Title	Valid From
10001	A	Whey Technician	Process Technician	03/08/2020
10001	A	Process Technician	Cheese Technician	01/03/2021
10001	A	Cheese Technician	Shift Manager	01/11/2021
10001	A	Shift Manager	Cheese Technician	01/03/2022
10002	B	Engineering Manager	Production Manager	01/11/2021
10002	B	Production Manager	Engineering Manager	01/01/2022

Hope this helps

[v-rzhou-msft · Accepted solution]

Hi @yaman123 ,

Try this code:

Table 2 = 
VAR _ADDRANK =
    ADDCOLUMNS (
        'Table',
        "Rank",
            RANKX (
                FILTER ( 'Table', 'Table'[Name] = EARLIER ( 'Table'[Name] ) ),
                'Table'[Valid From],
                ,
                ASC
            )
    )
VAR _SUMMARIZE =
    SUMMARIZE (
        _ADDRANK,
        [Emp No],
        [Name],
        [Rank],
        "Old Job Title", CALCULATE ( MAX ( 'Table'[Job Title] ) ),
        "New Job Title",
            MAXX (
                FILTER (
                    _ADDRANK,
                    [Name] = EARLIER ( [Name] )
                        && [Rank]
                            = EARLIER ( [Rank] ) + 1
                ),
                [Job Title]
            ),
        "Valid From",
            MAXX (
                FILTER (
                    _ADDRANK,
                    [Name] = EARLIER ( [Name] )
                        && [Rank]
                            = EARLIER ( [Rank] ) + 1
                ),
                [Valid From]
            )
    )
RETURN
    SUMMARIZE (
        FILTER ( _SUMMARIZE, [New Job Title] <> BLANK () ),
        [Emp No],
        [Name],
        [Old Job Title],
        [New Job Title],
        [Valid From]
    )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[yaman123]

Thanks so much @v-rzhou-msft. This gives me what I am looking for 🙂