First discount received

[Anonymous]

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

[parry2k]

@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.⚡

[Anonymous]

Thanks but it doesn't seem to work. There is an error 'A circulancy dependency was detected'

[Anonymous]

Thanks, I'll give it a try

[parry2k]

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

Thanks, appreciate it. It appears to be missing the filter for order status (Completed or processing)?

[parry2k]

@Anonymous what is a customer in that file? I don't see a customer column? Also, it is good to explain the expected output.

[Anonymous]

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. 

[parry2k]

@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.⚡

[Anonymous]

https://1drv.ms/x/s!ApVM2HyBcUjwlQYIUMPmc6FtPpzV?e=HMvuDR

[Ashish_Mathur · Accepted solution]

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]

Thanks, this works!!!

[Ashish_Mathur]

You are welcome.

[jaideepnema]

Hi @Anonymous ,
can you please share the dataset here ? It wouldn't be possible to get it from the image ?

[Anonymous]

I may be missing something but I can't seem to attach a file.