I have gross sales ytd, but I need to get net sales. There can be sales that happened in prior years that cancelled this year. How can I get net. I have a Sales Date Column and a Cancel Date column.
In other words I need a way to get the following, whether an equation or creating variables/tables:
Net Sales= Count(Sales Date)- Count(Cancel Date)
YTD Net Sales As of 5/2/2017 (Only select Year 2017 Sales Date)
A- 1
B-1
| Division | Sales Stage | Sale Date | Cancel Date |
|---|
A | Sold | 1/1/2017 | |
| B | Sold | 1/1/2017 | |
| B | Sold | 1/1/2017 | |
| A | Pending Sale | 5/1/2017 | |
| A | Cancel | 6/1/2015 | 3/1/2017 |
| A | Cancel | 2/1/2017 | 2/5/2017 |
| B | Canceled | 1/1/2017 | |
| A | Sold | 1/1/2017 | |
