Forum Discussion

SofiaTeles's avatar
SofiaTeles
Frequent Visitor
3 years ago

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

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   ...
Need Help
DOLEARY85's avatar
DOLEARY85
Icon for Resident Rockstar rankResident Rockstar
3 years ago

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 👍