Previous month calculated column for Text fields

[Tarun0055]

Hi ,

I have headcount table where I need to create a calculated column for employee team of previous month. Please find sample data and the expected  result.

Emp no	job effective date	team	previous team(calculated column)
1	01/01/2022	Sales
1	02/01/2022	Marketing	Sales
2	01/05/2022	sales
2	02/01/2022	sales	Sales
2	03/12/2022	Marketing	Sales

any help on this would be much appreciated.

Thanks 

[Jihwan_Kim · Accepted solution]

Hi, 

Please check the below picture and the attached pbix file.

It is for creating a new column.

Previous Team CC =
VAR _currentdate = Data[job effective date]
VAR _currentemp = Data[Emp no]
RETURN
    SUMMARIZE (
        FILTER (
            Data,
            Data[Emp no] = _currentemp
                && EOMONTH ( Data[job effective date], 0 ) = EOMONTH ( _currentdate, -1 )
        ),
        Data[team]
    )