Show Current and Previous Positions of Employee

[Justplainj]

Hi,

I have a data table with employee names, their positions and the effective date of the position.
The position of the employee changes when they are promoted in the company.

Example:
Name Position Date
Peter Pos1 1 Dec 2019
Peter Pos2 1 Mar 2015

How do I show the employee name, the current and previous position and the dates of those positions in one row of a table visual in PBI?

PS. Unfortunately due to data protection laws I cannot share a sample data.

Thanks in advance.

 

[Justplainj]

EDIT

the below solution only works if the employee has 2 positions (was promoted once) If they where promoted more than once (e.g. have 3 or 4 positions) then it shows the 2nd and last position instead of it current and previous.

Hi,

For those reading this thread. The solution was quite simple.

I have the following measure to give me the previous date.
PreviousPositionDate =
VAR MaxDate =
CALCULATE ( MAX ( 'Positions Condensed'[EffectiveDate] ), ALL ( 'Positions Condensed'[EffectiveDate] ) )
VAR PreviousDate =
CALCULATE ( MAX ( 'Positions Condensed'[EffectiveDate] ), 'Positions Condensed'[EffectiveDate] < MaxDate )
RETURN
IF ( ISBLANK ( PreviousDate ), MaxDate, PreviousDate )

I add the position, date and previous date in the table and then use summarize first and last in the order required to show the data as needed.

I hope someone else finds this useful.

 

[Justplainj]

Hi,

Answer here.

Created a new table with the following code.

Positions Old vs New Table =

VAR _ADDRANK =
    ADDCOLUMNS (
        'Positions Condensed',
        "Rank",
            RANKX (
                FILTER ( 'Positions Condensed', 'Positions Condensed'[FullName] = EARLIER ( 'Positions Condensed'[FullName] ) ),
                'Positions Condensed'[EffectiveDate],
                ,
                ASC
            )
    )
VAR _SUMMARIZE =
    SUMMARIZE (
        _ADDRANK,
        [FullName],
        [Rank],
        "IsPromotion",
                    MAXX(
                        FILTER(
                            _ADDRANK,
                            [FullName] = EARLIER( [FullName] )
                                && [Rank]
                                    = EARLIER( [Rank] ) + 1
                        ),
                        [IsPromotion]
                    ),
        "Old Job Title", CALCULATE ( MAX ( 'Positions Condensed'[OrganizationPosition] ) ),
        "New Job Title",
            MAXX (
                FILTER (
                    _ADDRANK,
                    [FullName] = EARLIER ( [FullName] )
                        && [Rank]
                            = EARLIER ( [Rank] ) + 1
                ),
                [OrganizationPosition]
            ),
        "Valid From",
            MAXX (
                FILTER (
                    _ADDRANK,
                    [FullName] = EARLIER ( [FullName] )
                        && [Rank]
                            = EARLIER ( [Rank] ) + 1
                ),
                [MinDate]
            )
    )
RETURN
    SUMMARIZE (
        FILTER ( _SUMMARIZE, [New Job Title] <> BLANK () ),
        [FullName],
        [Old Job Title],
        [New Job Title],
        [Valid From],
        [IsPromotion]
    )

My data has a IsPromotion Column also which was added to the code.