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

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 .

     

    • powerbifuddaa's avatar
      powerbifuddaa
      Helper II
      2 years ago

      Daniel29195 ,

      Thanks. Any idea how I directly can get this result:

      Person_BKFunctionChangedDateFunctionName@PreviousFunctionName
      5551-12-2020function A 
      5551-3-2022function Bfunction A
      5551-8-2022function Cfunction B
      55517-8-2023function Dfunction C
      11111-1-2014Medewerker Servicecenter 2 
      11111-1-2022Ondernemersadviseur 1Medewerker Servicecenter 2
      11111-3-2023Ondernemersadviseur 1Ondernemersadviseur 1
      111116-10-2023Adviseur Business Support 1Ondernemersadviseur 1
      11111-1-2024Adviseur Business Support 2Adviseur Business Support 1
      22221-1-2014Procesmanager 2 
      22221-1-2021Teamleider IT OperationsProcesmanager 2
      22221-3-2022Practice Lead InfraTeamleider IT Operations
      22221-10-2022Product OwnerPractice Lead Infra
      222215-3-2023Product Owner 1Product Owner
      22221-7-2023Product Owner 2Product Owner 1

      Thanks for your help.

      Kind regards, Elmer

      • Daniel29195's avatar
        Daniel29195
        Community Champion
        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]
        )