Forum Discussion
First discount received
Hi, I have a table with thousands of sales orders. I want a new column which calculates the amount of discount a customer received on their first order (ensuring first order status equals "processing" or "completed"). Below is a sample of the table (which has many more columns but I have reduced it to illustrate the problem).
Hi,
Write these calculated column formulas
First created date = CALCULATE(MIN(Data[date_created]),FILTER(Data,Data[Unique ID]=EARLIER(Data[Unique ID])&&(Data[Order Status]="Completed"||Data[Order Status]="Processing")))First discount received = LOOKUPVALUE(Data[discount_total],[date_created],[First created date],[Unique ID],[Unique ID],[Order Status],"Completed")Hope this helps.
14 Replies
Hi Anonymous ,
can you please share the dataset here ? It wouldn't be possible to get it from the image ?Anonymous share using One drive/google drive.
✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) 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.⚡
Hi,
Write these calculated column formulas
First created date = CALCULATE(MIN(Data[date_created]),FILTER(Data,Data[Unique ID]=EARLIER(Data[Unique ID])&&(Data[Order Status]="Completed"||Data[Order Status]="Processing")))First discount received = LOOKUPVALUE(Data[discount_total],[date_created],[First created date],[Unique ID],[Unique ID],[Order Status],"Completed")Hope this helps.
Anonymous what is a customer in that file? I don't see a customer column? Also, it is good to explain the expected output.
Unique ID is the customer ID. This is a full list of sales, For each row I would like to see the amount of discount that customer received for their first order. For wider context, It is apart of a churn model I am putting together, I am trying to see how long customers stay depending on the level of discount they received when they initially joined.
Anonymous add this measure:
first discount = CALCULATE ( SUM ( Sheet1[discount_total] ), ( TOPN ( 1, ALLEXCEPT ( Sheet1, Sheet1[Unique ID] ), ( MIN ( Sheet1[date_created] ) ), ASC ) ) )✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) 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.⚡
Anonymous maybe tweak it like this:
first discount = CALCULATE ( SUM ( Sheet1[discount_total] ), ( TOPN ( 1, FILTER ( ALLEXCEPT ( Sheet1, Sheet1[Unique ID] ), Sheet1[Order Status] IN { "Completed", "Processing" } ), MIN ( Sheet1[date_created] ), ASC ) ) )✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) 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.⚡
