Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I am using a pivot table to show sales by salesperson for accounts opened after a certain date. Dimensions are 'Salesperson' and 'AccountName'. Calculated Measures are 'Sales' and 'FirstSaleDate'. Example of the table below. I need to show only account names and data with a 'FirstSaleDate' after a certain date (for example 01/01/2022). Any thoughts and advice on how to accomplish this would be greatly appreciated. Thank you!
Salesperson | AccountName | First Sale | Sales |
John | Totals | 10/20/2021 | 110 |
Company A | 10/20/2021 | 10 | |
Company B | 11/20/2021 | 30 | |
Company C | 12/18/2021 | 50 | |
Company D | 01/20/2022 | 20 | |
Mary | Totals | 12/01/2021 | 145 |
Company F | 12/01/2021 | 80 | |
Company G | 02/18/2022 | 10 | |
Company H | 03/01/2022 | 25 | |
Company I | 03/04/2022 | 30 |
Hi, you can use set analysis to filter the dataset:
Sum({<[First Sale]={">=01/01/2022"}>} Sales)
Or to make it more dynamic using curren date: Sum({<[First Sale]={">=$(=Date(YearStart(Today())))"}>} Sales)