How to get previous value

[Anonymous]

I have a sample table as below:

Display_Name	Effective_Date	Department
Jason	2/1/2012	TCX
Jason	7/1/2012	TCX
Jason	7/1/2012	BFDA
Jason	8/14/2012	BFDA
Jason	8/21/2012	MBCL
Jason	1/19/2013	MBCL

I want to show all rows in a Table visual, and add a new column that show the previous row's value of Department, and this calculation should ORDER BY Effective_Date ASC, Department ASC. And please notice that some records may have same Effective_Date value, for example: 7/1/2012.

Due to real data source has huge size, so please use measure to realize it. Thanks.

The final result should like below:

Display_Name	Effective_Date	Department	Previous Department
Jason	2/1/2012	TCX
Jason	7/1/2012	TCX	TCX
Jason	7/1/2012	BFDA	TCX
Jason	8/14/2012	BFDA	BFDA
Jason	8/21/2012	MBCL	BFDA
Jason	1/19/2013	MBCL	MBCL

[danextian · Accepted solution]

Hi @Anonymous 

Please try this measure:

Previous Department = 
CALCULATE (
    MAX ( 'Table'[Department] ),
    OFFSET (
        -1,
        ALLEXCEPT ( 'Table', 'Table'[Display_Name] ),
        ORDERBY ( 'Table'[Effective_Date], ASC )
    )
)

[rajendraongole1]

Hi @Anonymous - you can achieve this using calculated column dax

I have created index column from power query editor and used the function as below

PreviousDepartment =

VAR CurrentIndex = 'pvys'[Index]

VAR CurrentName = 'pvys'[Display_Name]

RETURN

    CALCULATE(

        MAX('pvys'[Department]),

        FILTER(

            'pvys',

            'pvys'[Display_Name] = CurrentName &&

            'pvys'[Index] = CurrentIndex - 1

        )

    )

in visual

Hope it helps

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[Anonymous]

Is it possible to use measure? Because the real data size is huge.