Forum Discussion

powerbifuddaa's avatar
powerbifuddaa
Helper II
2 years ago
Solved

Previous function employee

Hello, I would like to make a calculated column with the PreviousFunctionName of an employee. Person_BK FunctionChangedDate FunctionName PreviousFunctionName 555 1-12-2020 function A nu...
Need Help
  • Daniel29195's avatar
    Daniel29195
    2 years ago

    ok i got you

    you needed to be partitioned by pperson_bk

     

     

    step0 : 

    you need to fix the column functionchangedate to respect the following format : 

    m-dd-yyyy

     

     

    step1 :  go to power query and change the column functionchangeddate type from text to date 

     

     

    step 3 : in power query sort asc the first column : 

     

    step 4 :  

    sort functionchangeddate column asc  

     

     

    save and apply. 

     

     

     

     

    step 5 : 
    use this measure now : 

    Column =
    var current_perosn = 'Table (7)'[Person_BK]
    var current_date = 'Table (7)'[FunctionChangedDate]
    var datasource =
        FILTER(
            all('Table (7)'[FunctionChangedDate],'Table (7)'[FunctionName],'Table (7)'[Person_BK]),
            'Table (7)'[Person_BK] = current_perosn && 'Table (7)'[FunctionChangedDate] <=current_date
            && 'Table (7)'[FunctionChangedDate] <=current_date
            )



    return  
    SELECTCOLUMNS(
    offset(
        -1,
        datasource,
        ORDERBY('Table (7)'[FunctionChangedDate] , asc ),
        PARTITIONBY('Table (7)'[Person_BK])
    ),
    [FunctionName]
    )
     
     

     

     

     

     

powerbifuddaa's avatar
powerbifuddaa
Helper II
2 years ago

Ashish_Mathur thank you for your help.

Maybe you can help me. Because your formula works fine when I make a separate tabel in powerbi with the columns I provided. But my model looks like this:

Table: Function with the columns FunctionName, Function_Key.

Table: Contract History with the columns Person_BK, FunctionChangeDate, Function_Key.

The relation is one (function) to many (contract history).

 

I tried to work with related:

LOOKUPVALUE (
    'Contract History'[@related_functionname],
    'Contract History'[FunctionChangedDate],
        CALCULATE (
            MAX ( 'Contract History'[FunctionChangedDate] ),
            FILTER (
                'Contract History',
                'Contract History'[Person_BK] = EARLIER ( 'Contract History'[Person_BK] )
                    && 'Contract History'[FunctionChangedDate]
                        < EARLIER ( 'Contract History'[FunctionChangedDate] )
            )
        ),
    'Contract History'[Person_BK], 'Contract History'[Person_BK]
)

 

I get a circular dependency message.

Any idea how I can solve this?

Thank you very much. Regards, Elmer

Ashish_Mathur's avatar
Ashish_Mathur
Super User
2 years ago

Hi,

Just by looking at your formula and table layout, i cannot help you.  Share the tables in a format that can be pasted in an MS Excel file and show the expected result.

  • powerbifuddaa's avatar
    powerbifuddaa
    Helper II
    2 years ago

    Hi Ashish_Mathur ,

    Here are the 2 tables:

    Function (table)Contract History (table) 
    FunctionNamePerson_BK
    Function_KeyFunctionChangeDate
     Function_Key

    The relation is one (function) to many (contract history).

     

    Expected result: calculated column PreviousFunctionName

    Person_BKFunctionChangedDateFunctionNamePreviousFunctionName
    5551-12-2020function Anull of empty
    5551-3-2022function Bfunction A
    5551-8-2022function Cfunction B
    55517-8-2023function Dfunction C

    Hope this helps. Please let me know if you expected something else I should have handed to you.

    Thank you very much. Regards, Elmer

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      2 years ago

      Hi,

      Share both tables in a format that can be pasted in an MS Excel file.

      • powerbifuddaa's avatar
        powerbifuddaa
        Helper II
        2 years ago

        Hi Ashish_Mathur  ,

        I am afraid that I do not know what you mean.  

        I have included the pbix file (PowerBI file). I hope that will help.

        Thanx and regards, Elmer