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

     

     

     

     

Daniel29195's avatar
Daniel29195
Community Champion
2 years ago

can you share the error  you are getting   ?

 

powerbifuddaa's avatar
powerbifuddaa
Helper II
2 years ago

Hi Daniel29195 

The error I am getting: parameter is not the correct type.

Thank you. Regards, Elmer

  • Daniel29195's avatar
    Daniel29195
    Community Champion
    2 years ago

    what is weird is that from my side i didnt get any error . 

    could you pllease try one of the following : 

    1. add the table name before the column name , so instead of  [FunctionName] , write :  Table_Name[FunctionName] ( with table_name =  the table name you have in your model ) 

    if this doesnt work,  

    try this : 

    SELECTCOLUMNS(
    offset(
        -1,
        datasource,
        ORDERBY('Table (4)'[FunctionChangedDate] , asc )
    ),
    "col" , Table_Name[FunctionName]
    )
     
    add the text in bold 
     
    let me know if it works for you
    • powerbifuddaa's avatar
      powerbifuddaa
      Helper II
      2 years ago

      Hi Daniel29195 ,

      I don't get an error anymore after adding the text in bold:

      This looks good.

       

      After testing and adding more person_bk's I get an output that I didn't expect:

      Person_BKFunctionChangedDateFunctionName@PreviousFunctionName
      5551-12-2020function AProcesmanager 2
      5551-3-2022function BOndernemersadviseur 1
      5551-8-2022function CPractice Lead Infra
      55517-8-2023function DProduct Owner 2
      111116-10-2023Adviseur Business Support 1function D
      11111-1-2024Adviseur Business Support 2Adviseur Business Support 1
      11111-1-2014Medewerker Servicecenter 2 
      11111-1-2022Ondernemersadviseur 1Teamleider IT Operations
      11111-3-2023Ondernemersadviseur 1Product Owner
      22221-3-2022Practice Lead Infrafunction B
      22221-1-2014Procesmanager 2Medewerker Servicecenter 2
      22221-10-2022Product Ownerfunction C
      222215-3-2023Product Owner 1Ondernemersadviseur 1
      22221-7-2023Product Owner 2Product Owner 1
      22221-1-2021Teamleider IT Operationsfunction A
       

      Any idea? Thank you for your help.

      Kind regards, Elmer

      • Daniel29195's avatar
        Daniel29195
        Community Champion
        2 years ago

        powerbifuddaa 

        yes,

        you need to sort the order of the table asc by functionchangedDate column , 

        since the offset function is base on the order of this column . 

         

         

        click on the arrow of the column and sort it ascending, 

         

         

        if you have any questions please post it and i will try to help you .