Sales and Returns in One Table

[mattramirez2020]

Hi! I have a dataset with each row representing a single sale. Each row has a sale date and a return date (if applicable). I am really struggling to generate a sales measure that generates the sales from a month and the returns that happened in that same month (March 2020 for example). I have been able to get this to work by duplicating the table and showing sales on one table and returns on another but I feel like there has to be a better way. 

Any help would be appreciated!!!

Sales and Returns Table:

Purchase_ID	Purchase_Date	Discount_Price	Return_Date	Customer_ID
100	1/1/20	1500	3/1/20	1
101	1/20/20	1900		2
102	3/1/20	7400	4/1/20	3

Desired Measure: Net Sales (anything within the currently selected filter range, for example, March 2020 less March 2020 returns) 7400-1500=5900.

I feel like an idiot for not being able to make something so simple work but I may be overthinking it. Everything I do only sums by purchase date-regardless of when the return was returned.

[jdbuchanan71 · Accepted solution]

@mattramirez2020 

The idea of what you are trying to do is pretty straight forward, it's the how that is a little difficut at first.  You will need a Dates table in your model, this is a dedicated table that holds the calendar for your model to use.  Then you link your Sales table to the Dates table using the [order date], this is the primary relationship.  You also link it using the return date, this is a secondary inactive relationship.

Then we can use the secondary relationship to calculate the return amount.

We start with a measure to sum the price.

Price = SUM ( Sales[Discount_Price] )

Then we write a measure to calculate the return price and that is where we activate the secondary relationship.

Return Amount = 
CALCULATE (
    [Price],
    USERELATIONSHIP ( Sales[Return_Date], Dates[Date] ),
    NOT ISBLANK ( Sales[Return_Date] )
)

Then a measure to calculate the difference.

Total Amount = [Price] - [Return Amount]

I have attached my sample file for you to look at.

[jdbuchanan71 · Accepted solution]

@mattramirez2020 

The idea of what you are trying to do is pretty straight forward, it's the how that is a little difficut at first.  You will need a Dates table in your model, this is a dedicated table that holds the calendar for your model to use.  Then you link your Sales table to the Dates table using the [order date], this is the primary relationship.  You also link it using the return date, this is a secondary inactive relationship.

Then we can use the secondary relationship to calculate the return amount.

We start with a measure to sum the price.

Price = SUM ( Sales[Discount_Price] )

Then we write a measure to calculate the return price and that is where we activate the secondary relationship.

Return Amount = 
CALCULATE (
    [Price],
    USERELATIONSHIP ( Sales[Return_Date], Dates[Date] ),
    NOT ISBLANK ( Sales[Return_Date] )
)

Then a measure to calculate the difference.

Total Amount = [Price] - [Return Amount]

I have attached my sample file for you to look at.

[mattramirez2020]

@jdbuchanan71  So this worked almost perfectly except I realized I am inflating sales and inflating returns because I don't take into account exchanges properly. I would love your feedback on the best way to handle exchanges if at all possible!!

In my example below, May sales would be $4895, June sales would be $0, June exchange difference would be -$895.

Purchase_ID	Purchase_date	Discount_Price	Return_Date	Exchange_Purchase_ID
100	1/1/20	1500	3/1/20	0
101	1/20/20	1900		0
102	3/1/20	7400	4/1/20	0
103	5/6/20	2895	6/8/20	0
104	5/6/20	2000		0
105	6/8/20	2000		103

Right now it gives me an output that nets out correctly but I would rather it be like this:

Basically the most important thing I am trying to accomplish to a way to reference on ID 105 that it is an exchange from purchase_ID 103 that does not include a $2895 in sales and then a $2000 in returns but just a -$895 in exchange difference. 

	Sales	Returns	+Exchanges	Net Sales
Jan	3400			3400
Feb				0
Mar	7400	1500		5900
Apr		7400		-7400
May	4895			4895
Jun			-895	-895

[jdbuchanan71 · Accepted solution]

@mattramirez2020 

The problem is Net Sales is [Sales] - [Returns + Exchanges] except in Jun the amount is - so that would make Net Sales + 895
0 - ( -895) and that is different than the calc for Apr.  You will see the Net Sales sign behavior does not match between those two months:

You can trick it with two new measures

Price no Exchanges = 
CALCULATE(
    SUM ( Sales[Discount_Price] )
    ,Sales[Exchange_Purchase_ID] = 0
)

Returns + Exchanges = 
VAR _ReturnNoExchange = 
    CALCULATE (
        SUM ( Sales[Discount_Price]),
        USERELATIONSHIP ( Sales[Return_Date], Dates[Date] ),
        NOT ISBLANK ( Sales[Return_Date] ),
        NOT Sales[Purchase_ID] IN VALUES ( Sales[Exchange_Purchase_ID] )
    )
VAR _ReturnWithExchange = 
    CALCULATE (
        SUM ( Sales[Discount_Price]),
        USERELATIONSHIP ( Sales[Return_Date], Dates[Date] ),
        NOT ISBLANK ( Sales[Return_Date] ),
        Sales[Purchase_ID] IN VALUES ( Sales[Exchange_Purchase_ID] )
    )
VAR _ExchangeFromReturn =  
    CALCULATE(
        SUM ( Sales[Discount_Price] )
        ,Sales[Exchange_Purchase_ID] <> 0
    )

RETURN _ReturnNoExchange + _ExchangeFromReturn - _ReturnWithExchange

You leave the total amount as the regular [Price] - [Returns] and you get something like this:

The green highlight just shows that the Total uses the base Price + Returns but you would not show that in your visual but again, the sign for Apr and Jun don't match.

What about showing it in 3 buckets?  Price w/o Exchanges, Returns w/o Exchanges, Exchanges.  Then the total is A - B + C and it at least looks more reasonable:

Anyway, I have attached my updated sample file, take a look.

[mattramirez2020]

@jdbuchanan71 This worked! So I realized I labeled my columns poorly and caused a ton of confusion. I like returns and exchanges being in their own columns and used your last format below. Apologies for the confusion but I appreciate you catching it and all of your help. I modified slightly and changed returns to being negative so now returns and exchange difference both show up negative and get added correctly for net amount.

[mattramirez2020]

 worked perfectly! This is so cool! Thank you @jdbuchanan71