Lookup a text value in a prior period in the same table for the same row

[bwarner87]

Dax Experts,

In short, I’m calculating retention during a time period. In order to make sure transfers into a department don’t affect my retention rate for a department, I’m looking to remove transfers from my remaining count. One of the initial steps though is identifying someone that transferred departments. My thought was to create a calculated column the looks up the DeptDesc value for an employee row 1 year or 1 month earlier. I would then compare the employee’s deptdesc in the end period to the start period (i.e. 1 year or 1 month ago) and if they match include them in my retention calc, else exclude.

Where I’m stuck: Get/Lookup the deptdesc 1 month earlier in the same table for the employee row.

Anticipated results with table structure in column “LastMonthDept”

Table Name = Turnover

*Note: there is a date table linked to the period column that don’t feel it’s necessary for the calculation.

Employee ID	DeptDesc	Period	LastMonthDept
12345	Dept 10	10/31/2021	Dept 10
12346	Dept 11	10/31/2021	Dept 11
12347	Dept 10	10/31/2021	Dept 10
12348	Dept 12	10/31/2021	Dept 12
12349	Dept 10	10/31/2021	Dept 10
12350	Dept 9	10/31/2021	Dept 9
12351	Dept 14	10/31/2021	Dept 14
12352	Dept 10	10/31/2021	Dept 10
12345	Dept 10	11/31/2021	Dept 10
12346	Dept 10	11/31/2021	Dept 11
12347	Dept 10	11/31/2021	Dept 10
12348	Dept 10	11/31/2021	Dept 12
12349	Dept 10	11/31/2021	Dept 10
12350	Dept 14	11/31/2021	Dept 9
12351	Dept 14	11/31/2021	Dept 14
12352	Dept 10	11/31/2021	Dept 10

Failed Attempts:

• After reviewing this video I tried working through lookup but either can get the wrong value using an aggregate (i.e. Maxx, etc..) or get a circular reference warning when I’m trying to feed previous month period value as a variable like below.  

LastMonthDept =

VAR PM = CALCULATE(PREVIOUSMONTH(Turnover[Period]),Turnover[Period])

RETURN

LOOKUPVALUE(

    Turnover[DeptDesc],

    Turnover[Employee ID],Turnover[Employee ID],

    Turnover[Period],PM

)

• After review this post I tried this formula but it’s populating for some records but not all and when I reviewed records missing a value one should be present.
  
  

LastMonthDept =

maxx(filter(Turnover,Turnover[Employee ID] = earlier(Turnover[Employee ID]) && Turnover[Period] = EARLIER(Turnover[Period])-30),Turnover[DeptDesc])

Thank you in advance!

[v-janeyg-msft · Accepted solution]

Hi, @bwarner87 

Accord to your description, I think you can create a measure.

Like this:

LastMonthDept =
VAR a = Turnover[Period]
VAR b =
    MAXX (
        FILTER (
            Turnover,
            Turnover[Employee ID] = EARLIER ( Turnover[Employee ID] )
                && Turnover[Period]
                    = DATE ( YEAR ( a ) - 1, MONTH ( a ), DAY ( a ) )
        ),
        Turnover[DeptDesc]
    )
RETURN
    IF ( b = BLANK (), Turnover[DeptDesc], b )

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,

Community Support Team _ Janey

[bwarner87]

@CNENFRNL ,

Thank you so much for this. I can verify that it does work for my use cases.

I'll try to explain in layman's terms as an exercise to help my understanding.

First you created a variable that stores a filtered table accounting for it matching the employee's ID AND that all period dates are in the past or less than the period is that row (i.e. earlier(turnover[period]).

you then used that variable in a return expression. where ou passwed in the table of rows for the employee and took the top row (i.e. TOPN 1) which is going to be the previous month because you ommitted an order value and so it would have the most recent date behine the value of the period in the row. You then used Maxx to retun the description from that row.

To make sure that I understood this I modified your DAX to grab the department 1 year earlier. I tried 2 methods but not able to get the 1 year. Can you help?

Failed Attempt # 1 -> I simply though i'd expand the returned rows to 12 and then take the MIN since it's ordered by descending. However,the only 2 aggregates (max and Min) will now be selecting from a mix of departments of a 12 month period and pulling back either the first or last based on alphabetical order, which is incorrect.

LY Dept =
VAR _lm =
FILTER(
Turnover,
Turnover[Employee ID] = EARLIER( Turnover[Employee ID] )
&& Turnover[Period] < EARLIER( Turnover[Period] )
)
RETURN
IF(
ISEMPTY( _lm ),
Turnover[DeptDesc],
MINX( TOPN( 12, _lm, Turnover[Period] ), Turnover[DeptDesc] )
)

Failed Attempe #2: I thought maybe int he filter expression i could manipulate the list of [period] dates to start 1 year earlier and then grab that max date. This gave me somehow the latest department which is incorrect. 

LY Dept =
VAR _lm =
FILTER(
Turnover,
Turnover[Employee ID] = EARLIER( Turnover[Employee ID] )
&& MAXX(DATESINPERIOD(Turnover[Period],EARLIER(Turnover[Period]),-1,YEAR),Turnover[Period])
)
RETURN
IF(
ISEMPTY( _lm ),
Turnover[DeptDesc],
MAXX( TOPN( 1, _lm, Turnover[Period] ), Turnover[DeptDesc] )
)

Failed Attempt #3: I then thought if I could create 2 variables representing 11 rows and 12 rows sorted by PERIOD in descending order i could extract out the 12th row by using the EXCEPT function. However, when i tried this it's giving me a circular dependency issue which I'm not sure how to remediate.

LY Dept =
VAR _lm =
FILTER(
Turnover,
Turnover[Employee ID] = EARLIER( Turnover[Employee ID] )
&& Turnover[Period] < EARLIER( Turnover[Period] )
)
VAR TBL12 =
TOPN( 12, _lm, Turnover[Period] )
VAR TBL11 =
TOPN( 11, _lm, Turnover[Period] )

RETURN
IF(
ISEMPTY(_lm),
Turnover[DeptDesc],
MAXX(EXCEPT(TBL11,TBL12), Turnover[DeptDesc])
)

Any suggestions on how to get 1 year back on the DeptDesc instead of 1 month?

[CNENFRNL]

LM Dept = 
VAR _lm =
    FILTER(
        Turnover,
        Turnover[Employee ID] = EARLIER( Turnover[Employee ID] )
            && Turnover[Period] < EARLIER( Turnover[Period] )
    )
RETURN
    IF(
        ISEMPTY( _lm ),
        Turnover[DeptDesc],
        MAXX( TOPN( 1, _lm, Turnover[Period] ), Turnover[DeptDesc] )
    )