Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to 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))))
Hi @sagadgreat
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))))
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |