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

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   ?

     

    • 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