Calculated column displaying the first and last value/text shown in another column.

[SofiaTeles]

Hi, 

Please consider this sample purchases dataset: 

Client	Purchase Date	Order code	Promocode
A	14/04/2023	A001	code40
B	15/04/2023	A002	code50
C	16/04/2023	A003
D	17/04/2023	A004
A	18/04/2023	A005	code40
B	19/04/2023	A006	code30
C	20/04/2023	A007	code30
D	21/04/2023	A008
A	22/04/2023	A009
C	23/04/2023	A010

I'm trying to analyze the performance of the promo codes. I want to determine if customers continue to make purchases from us after using a promocode. To do this, I plan to create two calculated columns: "first_promocode" and "last_promocode" (as a customer may use multiple promo codes, I haven't decided yet which one to consider). I want these columns to be associated with the customer. The expected output is as follows:

Client	Purchase Date	Order code	Promocode	First Promocode	Last promocode
A	14/04/2023	A001	code40	code40	code40
B	15/04/2023	A002	code50	code50	code30
C	16/04/2023	A003		code30	code30
D	17/04/2023	A004
A	18/04/2023	A005	code40	code40	code40
B	19/04/2023	A006	code30	code50	code30
C	20/04/2023	A007	code30	code30	code30
D	21/04/2023	A008
A	22/04/2023	A009		code40	code40
C	23/04/2023	A010		code50	code30

Can you please help me find a DAX function for this? I haven't been very successful on my own.

Thank you in advance.

Best regards, 

Sofia Teles

[SofiaTeles]

Hi @DOLEARY85 

Thank you so much for your quick reply.

Power Bi is returning this error: "A multi-value table was provided where a single value was expected."

Do you know how can I solve it? I might have simplified the dataset presented here compared to the one I'm using.

Thanks in advance...

Best regards, 

Sofia

[DOLEARY85]

Hi,

i don't suppose you can share the PBIX file?

[SofiaTeles]

Yes, I cannot...

[DOLEARY85]

I'd assume the issues was based on duplicates on the join in the table, however i tried duplicating a row in my test table and it's still working.

Do you have an anonymised data set i could look at?

[DOLEARY85]

Hi, 

try these calculated columns:

First Promo Used = CALCULATE(LOOKUPVALUE('Table (6)'[Promocode],'Table (6)'[Purchase Date],CALCULATE(MIN('Table (6)'[Purchase Date]))),FILTER(ALLEXCEPT('Table (6)','Table (6)'[Client]),'Table (6)'[Promocode]<>BLANK()))

Last Promo Used = CALCULATE(LOOKUPVALUE('Table (6)'[Promocode],'Table (6)'[Purchase Date],CALCULATE(MAX('Table (6)'[Purchase Date]))),FILTER(ALLEXCEPT('Table (6)','Table (6)'[Client]),'Table (6)'[Promocode]<>BLANK()))

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍