Search value using conditions

[arnaudrueff]

Hi everyone

I need help with a calculated column formula :

I have a table with three columns : names of employees, current assignment and current month

[Employee]  [Assignment] [Month]

EmployeeA  NewYork January

EmployeeB  LosAngeles January

EmployeeC  Miami January

EmployeeA  LosAngeles February

EmployeeB  NewYork February

EmployeeC  Miami February

I need to track changes in assignments month to month. In the current example, in February, Employee A was reassigned to the LosAngeles office and Employee B to the NewYork office.

To do this, I would like to add a calculated column in the table that would return on each row the value of the assingment of the previous month for the same employee. For example :

[Employee]  [Assignment] [Month] [Newcalculatedcolumn]

EmployeeB NewYork February LosAngeles

On a given month, an employee can only have one assignment; data is clean in this regard.

I've been through the various forums for hours and was unsuccessful with the calculate, filter and date functions. Most examples show how to compute a YTD and LYTD sum, but I couldn't find how to return a text value. I'm a complete beginner. If anyone could help, that would be much appreciated.

Thanks a lot.

[v-yulgu-msft]

Hi @arnaudrueff,

You can also achieve that in View mode using DAX.

First, change the [Month] column to numeric.

Month No =
SWITCH (
    'Search Value'[Month],
    "January", 1,
    "February", 2,
    "March", 3,
    "April", 4,
    "May", 5,
    "June", 6,
    "July", 7,
    "Auguest", 8,
    "September", 9,
    "October", 10,
    "November", 11,
    "December", 12
)

Add a calculate column to display previous month number.

Previous Month No = 'Search Value'[Month No]-1

Use a LookUpValue function to fetch the value of assingment of the previous month for the same employee.

Previous Assignment =
LOOKUPVALUE (
    'Search Value'[Assignment],
    'Search Value'[Month No], 'Search Value'[Previous Month No],
    'Search Value'[Employee], 'Search Value'[Employee]
)

Best regards,
Yuliana Gu

[ImkeF · Accepted solution]

If you use a number-representation for your months, it's relatively easy:

1) Add a column where you calculate the previous month (-1)

2) Merge the table with itself on the 2 number columns: Current month with previous month per employee and expand the location.

Here is a short video: