Tracking sales vs. returns made in consecutive months

[sagadgreat]

I have a table where there are sales vs. returns made in consecutive months anda  formula that is calculating total extended sales. 

If I put both months in using the positive and negative qtys (see table example), it works fine to remove or zero out the item from total sales as being returned.  If I filter to February however, I get the -revenue with no cost associated, skewing calculations for gross margin, etc.

My thought is for the following month, when that return occurs it should zero out the previous sale for both cost and total revenue, removing it from the equation for Gross Profit and Margin calculations.  I have tried both multiplying by the negative qty and making absolutes but just can't seem to get it to work.

I am a newbs newb and appreciate any guidance as this is my first post to the community:

Customer Name	Date	Qty Purchased	Cost	Total Revenue
ABC Company	1/12/2020	1	10	100
ABC Company	2/12/2020	-1	10	-100

[v-juanli-msft]

Hi @sagadgreat 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case and help the other members find it more quickly?

If not, please feel free to let me know.

Best Regards

Maggie

[amitchandak · Accepted solution]

I did not get it completely. But do you want to combine this month's sales with next month's return? you can have formula's like

MTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))
next MTD (complete) return =  CALCULATE(SUM(Return[Return Amount])*-1,DATESMTD(ENDOFMONTH(dateadd('Date'[Date],1,MONTH))))