Forum Discussion
Data from previous years not appearing when filtering
I managed to solve the issue.
I create a new column that will chose the relevant date. If the project has been closed - won, it will take the date from closedate column otherwise use original_quote_date.Relevant_Date = IF('result_deals'[dealstage] = "Closed - Won", 'result_deals'[closedate], 'result_deals'[original_quote_date] )
I'm using the above column for filtering and using the relevant date column as the date slicer.
Sahir_Maharaj danextian Ashish_Mathur
I've tried both solutions but they still don't seem to work.
I've tried creating a centralized date table and making the active relationship to original quote date but it's still not working.
Due to data privacy and working with real information, below is sample of my data (it's not allowing me upload a CSV file)
I have highlighted project 12 an example.
The goal is to create a conversion rate of quotes submitted to projects won. Then, in a line graph display and card visual show the total projects won and projects quoted. Finally, use a date slicer to narrow down and see the total project won and quoted in a set period (Year, QTR, and Month)
| ProjectName | deal_currency_code | createdate | Country | CO# | closed_lost_reason | closedate | dealstage | del_start | order_due | original_quote_date | Sum of unit_amount |
| project 2 | EUR | 16/04/2024 12:51 | IRE | 29373323412 | 14/11/2024 15:15 | Closed - Won | 01/04/2025 00:00 | 01/01/2025 00:00 | 07/05/2024 00:00 | 65 | |
| project 3 | EUR | 27/09/2024 09:52 | IRE | 29382040502 | 29/11/2024 12:33 | Closed - Won | 03/03/2025 00:00 | 04/11/2024 00:00 | 18/10/2024 00:00 | 461 | |
| project 4 | EUR | 17/06/2024 12:58 | IRE | 29385007545 | 29/11/2024 15:06 | Closed - Won | 01/05/2025 00:00 | 01/11/2024 00:00 | 09/07/2024 00:00 | 146 | |
| project 5 | GBP | 29/01/2024 09:12 | UK | 29343585074 | Project Cancelled | 12/12/2024 12:01 | Closed - Lost | 19/02/2024 00:00 | 271 | ||
| project 6 | GBP | 04/03/2024 09:01 | UK | 29363593221 | Project Went Traditional | 12/12/2024 15:26 | Closed - Lost | 25/03/2024 00:00 | 111 | ||
| project 7 | EUR | 03/12/2024 15:53 | IRE | 29794206393 | Waiting Response | 23/02/2023 00:00 | 154 | ||||
| project 8 | GBP | 01/02/2023 12:26 | UK | 29355474285 | Quote Submitted | 23/02/2023 00:00 | 272 | ||||
| project 9 | GBP | 03/02/2023 12:05 | UK | 29700203912 | Quote Submitted | 24/02/2023 00:00 | 645 | ||||
| project 10 | EUR | 04/02/2023 12:55 | IRE | 29377504819 | Quote Submitted | 25/02/2023 00:00 | 164 | ||||
| project 11 | GBP | 08/01/2024 14:56 | UK | 30231210568 | Competitors | 01/02/2024 14:56 | Closed - Lost | 25/01/2024 00:00 | 333 | ||
| project 12 | EUR | 20/11/2023 16:11 | IRE | 30362596895 | 28/02/2024 16:14 | Closed - Won | 03/06/2024 00:00 | 11/12/2023 00:00 | 689 | ||
| project 13 | EUR | 08/04/2024 10:41 | IRE | 29762598472 | Project Went Traditional | 30/04/2024 15:03 | Closed - Lost | 02/09/2024 00:00 | 29/04/2024 00:00 | 538 | |
| project 14 | EUR | 01/02/2024 10:47 | IRE | 29675180097 | 31/05/2024 12:29 | Closed - Won | 02/09/2024 00:00 | 22/02/2024 00:00 | 107 | ||
| project 15 | EUR | 22/08/2024 12:45 | IRE | 29679128651 | 15/06/2024 12:26 | Closed - Won | 03/06/2024 00:00 | 12/09/2024 00:00 | 213 | ||
| project 16 | GBP | 09/04/2024 10:47 | UK | 29674186626 | 01/07/2024 12:18 | Closed - Won | 03/03/2025 00:00 | 30/04/2024 00:00 | 207 | ||
| project 17 | EUR | 14/05/2024 10:36 | IRE | 29675181415 | 25/09/2024 15:14 | Closed - Won | 01/05/2025 00:00 | 04/06/2024 00:00 | 183 | ||
| project 18 | EUR | 09/05/2024 12:45 | IRE | 29679125549 | 08/10/2024 15:16 | Closed - Won | 01/10/2024 00:00 | 30/05/2024 00:00 | 173 |