cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bwarner87
Frequent Visitor

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

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!

1 ACCEPTED SOLUTION
v-janeyg-msft
Community Support
Community Support

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

View solution in original post

3 REPLIES 3
v-janeyg-msft
Community Support
Community Support

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

View solution in original post

bwarner87
Frequent Visitor

@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
Community Champion
Community Champion

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] )
    )

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.