Filter table Lookupvalue based on dates

[pmmsb]

Hi everyone,

I would like to start off by thanking the support on the forums. I've made an account just now but have been getting tips for some months now. You guys save us a bucket load of time and pain.

Now, to the point:

I'm having some problems during an analysis that requires filtering on more than one column.

I have two tables: 'Estimates' and 'Sales'.

Both have 'Customer ID' and 'Date' - I want to measure the estimate success by measuring the sales made on that Customer ID up to one month after the value was given to the client.

I was able to get a calculated column on the Sales table with Lookupvalue for the Customer ID of 'Estimates' table, as shown below:

HasEstimate = IF(LOOKUPVALUE('Estimate'[CustomerID],'Estimate'[CustomerID],'Sales'[CustomerID]=blank(), "NO", "YES")

The problem is that even when the customer hasn't asked for an estimate but has made two orders, it still shows "YES"; also, how can I get data filtered between 1 month prior to 'Estimate'[DateCreated] and 1 month after?

The tables are connected through [CustomerID] and to a third table 'Calendar':

Calendar = 
VAR MinDate = MIN('Sales'[Date])
VAR MaxDate = MAX('Sales'[Date])

RETURN

CALENDAR(
    DATE(YEAR(MinDate), 1, 1),
    DATE(YEAR(MaxDate), 12, 31)
    )

Thanks in advance.

Cheers,

PB

[pmmsb]

Marked as new to request assistance.

[lbendlin]

It is unclear what you are trying to measure.  Can only zero or one estimate can be made per customer?  If there is no estimate for a customer then should the sales to them be ignored? What shoud happen if the estimate is made after the sale?  How do you calculate the denominator?

Your sample data has the same dates for estimate and sale. Please adjust it to be more realistic.

[pmmsb]

Hi again,

Edited the date stamps on the 'Sales' table to be more realistic.

In terms of estimate and sales, no max limit exists.

Think of it in terms of a service company: you can ask for several pricings to check if they match your budget; if they don't, you'll feel the need to ask for prices elsewhere.

I am trying to measure the acceptance rate on how many estimates are made.

As they have to be made (the client contacts us, we have to fulfill the client's needs), we are trying to capitalize that.

The key to calculare the numerator is to check which estimates were accepted and provided a sale, while the denominator is the total amount of estimates given.

In this case, only the clients which asked for a previous quotation on service are enlisted for this analysis.

P.S.: I dunno why, but when I tried to edit my reply, it gave a 'Solved' status to this topic. Please disregard it as a false one.

Cheers,

PB

[lbendlin]

Fixed that for you.

If you can have multiple estimates and multiple sales then I see no way to correlate a particular sale with a particular estimate.  Would you only consider a pair estimate+sale if they follow each other on the time axis? What if you have multiple estimates on the same day? Multiple sales on the same day? 

I combined the sample data into one table. Here is how it looks like per customer

What is your expected result?  75% ?

[lbendlin]

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to work with. Avoid posting screenshots of your source data if possible.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[pmmsb]

Thank you for your answer.

For some reason, I've tried posting the data for 3 times without success.

Here's an example of the data I have:

Table 'Estimates'

Store ID	#Estimate	Customer ID	Date_Created [DD/MM/YYYY]	Net Value [$]
A	590/2022	AA18AA	10/02/2022	100
A	1021/2022	AA29BB	18/03/2022	150
B	12/2022	85RX81	05/01/2022	600
C	185/2022	AG23PT	19/01/2022	1500
D	230/2022	12BX10	12/02/2022	3840

Table 'Sales'

Store ID	#WIP	Customer ID	Date_Sale [DD/MM/YYYY]	Net Value [$]
A	480/2022	AA18AA	17/02/2022	100
A	901/2022	65TV12	25/03/2022	1500
B	18/2022	85RX81	03/02/2022	200
C	189/2022	AG23PT	21/01/2022	1200
D	230/2022	12BX10	15/02/2022	3840

The analysis I am trying to get is:
"If 'Customer_ID' is found in 'Sales' and is present in 'Estimates' within 1 month of 'Date_Created', then it counts towards the objective:

The relationships present are the following:

Here, 'Matrículas' stands for Customer_ID, 'Data_Vendas' is 'Date_Sale', 'Orçamentos' is 'Estimates' and 'Vendas Oficina' is 'Sales'.

Thanks for your help.

Cheers,

PB