cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LuisELopez
Helper II
Helper II

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 below. 

 

Thank you

 

 

User ProductDateFirstProductSecondProduct
AKey101/01/2020 14:00Key1Key2
AKey102/01/2020 14:00Key1Key2
BKey203/01/2020 14:00Key2 
AKey204/01/2020 14:00Key1Key2
CKey105/01/2020 14:00Key1Key3
CKey306/01/2020 14:00Key1Key3
CKey407/01/2020 14:00Key1Key3
AKey408/01/2020 14:00Key1Key2
DKey710/01/2020 14:00Key7Key1
DKey115/01/2020 14:00Key7Key1
1 ACCEPTED SOLUTION
Ashish_Mathur
Super User III
Super User III

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

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User III
Super User III

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

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Mikelytics
Resolver III
Resolver III

Hi @LuisELopez 

 

Please try the following:

 

first product = 
SELECTCOLUMNS(
    TOPN(
        1,
        FILTER(ALL(fact_table_d),
            fact_table_d[Customer]=EARLIER(fact_table_d[Customer])
        )
        ,
        fact_table_d[Date],
    ASC 
    )
,"any name, it does not matter"
,fact_table_d[Product]
)

This calulcated column identifies all rows of the customer and sorts it by date. Then it takes value of the product column from the first row which is the first product bought by the customer.

 

for the seconf product use the following

second product = 
SELECTCOLUMNS(
    TOPN(
        1,
        FILTER(ALL(fact_table_d),
            fact_table_d[Customer]=EARLIER(fact_table_d[Customer])
            && fact_table_d[Product] <> EARLIER(fact_table_d[first product])
        )
        ,
        fact_table_d[Date],
    ASC 
    )
,"any name"
,fact_table_d[Product]
)

It is nearly the same query with the only exception that it also filters out the first product bought. So taht if somebody bought the same product twice in a row it will be ignored.

 

Best regards

Mikelytics

 

Did I solve your request? Please mark my post as solution.

 

Appreciate your Kudos.

 

 

parry2k
Super User III
Super User III

@LuisELopez seems like your business rule for 1st and 2nd product is to get based on the date within each user group.

 

See attached solution, you can tweak it as you see fit.

 

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





VijayP
Super User I
Super User I

@LuisELopez 

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




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.