Previous function employee

[powerbifuddaa]

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	null of empty
555	1-3-2022	function B	function A
555	1-8-2022	function C	function B
555	17-8-2023	function D	function C

I can use DAX only.

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

[Ashish_Mathur]

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]

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

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]

Hi @Ashish_Mathur ,

Here are the 2 tables:

Function (table)	Contract History (table)
FunctionName	Person_BK
Function_Key	FunctionChangeDate
	Function_Key

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

Expected result: calculated column PreviousFunctionName

Person_BK	FunctionChangedDate	FunctionName	PreviousFunctionName
555	1-12-2020	function A	null of empty
555	1-3-2022	function B	function A
555	1-8-2022	function C	function B
555	17-8-2023	function D	function 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]

Hi,

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

[powerbifuddaa]

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

[Ashish_Mathur]

Hi,

Your question has already been answered by another user.

[Daniel29195]

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]

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]

can you share the error  you are getting   ?

[powerbifuddaa]

Hi @Daniel29195 

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

Thank you. Regards, Elmer

[Daniel29195]

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]

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_BK	FunctionChangedDate	FunctionName	@PreviousFunctionName
555	1-12-2020	function A	Procesmanager 2
555	1-3-2022	function B	Ondernemersadviseur 1
555	1-8-2022	function C	Practice Lead Infra
555	17-8-2023	function D	Product Owner 2
1111	16-10-2023	Adviseur Business Support 1	function D
1111	1-1-2024	Adviseur Business Support 2	Adviseur Business Support 1
1111	1-1-2014	Medewerker Servicecenter 2
1111	1-1-2022	Ondernemersadviseur 1	Teamleider IT Operations
1111	1-3-2023	Ondernemersadviseur 1	Product Owner
2222	1-3-2022	Practice Lead Infra	function B
2222	1-1-2014	Procesmanager 2	Medewerker Servicecenter 2
2222	1-10-2022	Product Owner	function C
2222	15-3-2023	Product Owner 1	Ondernemersadviseur 1
2222	1-7-2023	Product Owner 2	Product Owner 1
2222	1-1-2021	Teamleider IT Operations	function A

Any idea? Thank you for your help.

Kind regards, Elmer

[Daniel29195]

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

@Daniel29195 ,

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

Person_BK	FunctionChangedDate	FunctionName	@PreviousFunctionName
555	1-12-2020	function A
555	1-3-2022	function B	function A
555	1-8-2022	function C	function B
555	17-8-2023	function D	function C
1111	1-1-2014	Medewerker Servicecenter 2
1111	1-1-2022	Ondernemersadviseur 1	Medewerker Servicecenter 2
1111	1-3-2023	Ondernemersadviseur 1	Ondernemersadviseur 1
1111	16-10-2023	Adviseur Business Support 1	Ondernemersadviseur 1
1111	1-1-2024	Adviseur Business Support 2	Adviseur Business Support 1
2222	1-1-2014	Procesmanager 2
2222	1-1-2021	Teamleider IT Operations	Procesmanager 2
2222	1-3-2022	Practice Lead Infra	Teamleider IT Operations
2222	1-10-2022	Product Owner	Practice Lead Infra
2222	15-3-2023	Product Owner 1	Product Owner
2222	1-7-2023	Product Owner 2	Product Owner 1

Thanks for your help.

Kind regards, Elmer

[Daniel29195 · Accepted solution]

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]

Thanks @Daniel29195 

Although I get an error again (Parameter is not the correct type), this works!

If I remove the "PARTITIONBY('Table (2)'[Person_BK])" part, it also works.

Thank you for your help

[Daniel29195]

@powerbifuddaa 
glad i could help you out .

Feel free to mention my name in your future questions within the community. I would be more than happy to assist you further.