Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

First and Second Product

Hi, everyone, im trying to calculate a new column for the first and second product, I have 3 columns as User, Product and Date, what I want is the "FirstProduct" and "SecondProduct" as the example be...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    Hi,

    Try these calculated column formulas

    First date of user = 

    =CALCULATE(MIN(Data[Date]),FILTER(Data,Data[User]=EARLIER(Data[User])))

    First product = 

    LOOKUPVALUE(Data[Product],Data[User],Data[User],Data[Date],Data[First date of user])

    Second date of user = 

    CALCULATE(MIN(Data[Date]),FILTER(Data,Data[User]=EARLIER(Data[User])&&Data[Date]>Data[First date of user]&&Data[Product]<>EARLIER(Data[First product])))

    Second product = 

    =LOOKUPVALUE(Data[Product],Data[User],Data[User],Data[Date],Data[Second date of user])

VijayP's avatar
VijayP
Icon for Community Champion rankCommunity Champion
5 years ago

Anonymous 

I think if you can explain the logic of First and 2nd Columns then solution will be much easier, What is the basis of the new columns