Data from previous years not appearing when filtering

[Kacper3]

My issue is the data is inaccurate when I filter by date.

Context: 

The report shows:

• The total won projects (This is based on dealstage = "Closed - Won")

• Total quotes submitted (I'm using the original quote date to stamp that the project has been through the quote stage. The projects move out of the quote stage and update, so using "Dealstage" will be inaccurate)

• Then I'm dividing both to get a conversion rate.

I've tried using the original quote date for filtering but there's project's quoted in 2023 that aren't appearing in the won projects measure.

When I filter by close date, it only shows me a total of projects that have a close date and original quote date, either won or lost. 

My goal is to display a conversion rate and total projects won each year while displaying projects we've won even if they've been quoted in 2023. 

Here's my measures:

06 - Total Win = 

CALCULATE(COUNTROWS('result_deals'),
'result_deals'[dealstage] = "Closed - Won"
)


03 - ConversionRate = 
DIVIDE(
  [06 - Total Win], [20 - Total_QS]
)

20 - Total_QS = 
CALCULATE(
    COUNTROWS('result_deals'),
    NOT(ISBLANK('result_deals'[original_quote_date])) || result_deals[dealstage] = "Quote Submitted"
)

What can I do differently?

Thanks

[Kacper3 · Accepted solution]

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. 

[Kacper3]

@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

[danextian]

Hi @Kacper3 

While measures can contain aggregates outside the selected range, the way filters from a related or the same table work is that it shows only the rows based on what's been selected such that if you select 2024, it will show just all 2024rows. You can either change the numeric slicer to before and reference the max year in your measure or use a disconnected table to show even those that are not from the current year using still by referencing it in a measure.

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[Sahir_Maharaj]

Hello @Kacper3,

Can you please try this:

06 - Total Win = 
CALCULATE(
    COUNTROWS('result_deals'),
    'result_deals'[dealstage] = "Closed - Won"
)

20 - Total_QS = 
CALCULATE(
    COUNTROWS('result_deals'),
    OR(
        NOT(ISBLANK('result_deals'[original_quote_date])),
        'result_deals'[dealstage] = "Quote Submitted"
    )
)

03 - ConversionRate = 
DIVIDE(
    CALCULATE(
        [06 - Total Win],
        'result_deals'[CloseYear] = SELECTEDVALUE('Calendar'[Year])
    ),
    CALCULATE(
        [20 - Total_QS],
        'result_deals'[QuoteYear] = SELECTEDVALUE('Calendar'[Year])
    )
)

QuoteYear = YEAR('result_deals'[original_quote_date])

CloseYear = YEAR('result_deals'[close_date])