Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

How to get previous value

I have a sample table as below:

Display_NameEffective_DateDepartment
Jason2/1/2012TCX
Jason7/1/2012TCX
Jason7/1/2012BFDA
Jason8/14/2012BFDA
Jason8/21/2012MBCL
Jason1/19/2013MBCL


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_NameEffective_DateDepartmentPrevious Department
Jason2/1/2012TCX 
Jason7/1/2012TCXTCX
Jason7/1/2012BFDATCX
Jason8/14/2012BFDABFDA
Jason8/21/2012MBCLBFDA
Jason1/19/2013MBCLMBCL
Need Help
  • danextian's avatar
    danextian
    2 years ago

    Hi Anonymous 

     

    Please try this measure:

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

     

     

3 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

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

  • danextian's avatar
    danextian
    Super User
    2 years ago

    Hi Anonymous 

     

    Please try this measure:

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