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_BKFunctionChangedDateFunctionNamePreviousFunctionName
5551-12-2020function Anull of empty
5551-3-2022function Bfunction A
5551-8-2022function Cfunction B
55517-8-2023function Dfunction C

 

I can use DAX only.

Could somebody help me with this challenge?
Thank you! Regards, Elmer

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]
    )
     
     

     

     

     

     

18 Replies

  • Daniel29195's avatar
    Daniel29195
    Community Champion
    2 years ago

    try this code  : 

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



    return  
    SELECTCOLUMNS(
    offset(
        -1,
        datasource,
        ORDERBY('Table (4)'[FunctionChangedDate] , asc )
    ),
    [FunctionName]
    )
    • powerbifuddaa's avatar
      powerbifuddaa
      Helper II
      2 years ago

      Thank you for your respons and help Daniel29195.

      Unfortunately this code doesn't work for me. I get an error after selectcolumns(..., [name1],...

      Also I'm missing function c in your output. 

      • Daniel29195's avatar
        Daniel29195
        Community Champion
        2 years ago

        can you share the error  you are getting   ?

         

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    Hi,

    This calculated column formula works

    =LOOKUPVALUE(Data[FunctionName],Data[FunctionChangedDate],CALCULATE(MAX(Data[FunctionChangedDate]),FILTER(Data,Data[Person_BK]=EARLIER(Data[Person_BK])&&Data[FunctionChangedDate]<EARLIER(Data[FunctionChangedDate]))),[Person_BK],[Person_BK])

    Hope this helps.

     

    • 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.