Hitrate from two dates problem

[jussiwaisto]

Hi,

I have a table (offers to customers) where are two date datas and if i want to count a hitrate from one month (deals made in that month / offers made in that month) I should made a count from two date datas. 

1. Offer creation date

2. Deal confirmation date

Example:

If I want to count hitrate from february (confirmed dates from febryary / offers made in february) I face to problem: I should use two different date slicers but If i put there data slicer about "Offer date" and slicer about "Confirmation date", the system filters only 1 confirmed offer row. I should use two separate dates which won't filter each other.

Customer	Offer date	Deal confirmation date
Cust1	1.1.2019
Cust2	2.1.2019
Cust3	3.1.2019
Cust4	4.1.2019	6.2.2019
Cust5	5.1.2019
Cust6	1.2.2019
Cust7	2.2.2019
Cust8	3.2.2019
Cust9	4.2.2019
Cust10	5.2.2019
Hitrate in february = 1 / 5 = 0,2 = 20% (cust4 / cust6+7+8+9+10)

How do I solve this problem?

[iplaygod]

Hi

I am not 100% sure exactly what filters you want to apply.

If you want to make a count of all offers made within a particular month, and count all customers where Deal confirmation is within the same month

Presuming that you are using a Calendar / Date table

Presuming that "Customer" is in fact a Customer ID....

You can create 2 measures:
Count customers Received Offer =

VAR currMinDate = MIN('CalendarTable'[Date])
VAR currMaxDate = MAX('CalendarTable'[Date])
RETURN
CALCULATE(
DISTINCTCOUNT( yourTable[Customer]),
FILTER(ALL( yourTable), 
   yourTable[Offer Date] >= currMinDate
   && yourTable[Offer Date] <= currMaxDate
)
)

Then you create another almost identical measure but adapt the code so that its working on the Deal confirmation date column instead

sorry for any typos cannot test live right now

If you DO NOT have a date table I highly encourage you to learn about date tables and get one implemented in your model.

But, you can make it work without it by replacing some code in the measure:

Count customers Received Offer =

VAR currMinDate = MIN(yourTable[Offer Date])
VAR currMaxDate = MAX(yourTable[Offer Date])
RETURN
CALCULATE(
DISTINCTCOUNT( yourTable[Customer]),
FILTER(ALL( yourTable), 
   yourTable[Offer Date] >= currMinDate
   && yourTable[Offer Date] <= currMaxDate
)
)

This might get wrong results if your column does not actually contain all the dates of the month.

(which might happen if there are no offers betw 20-30th of the month for example)

this is why you really need a date table to get correct results.

[jussiwaisto]

Thank you very much for your answer! :)))

I figured out how to make a date table and I did it (I also made relationships from the Offers -table's "Offer date"-column to Date table's "Date" -column. (Real world table name is 'ekoseer Sopimus' and columns are [Allekirjoituspvm] and [Pvm].) Is this right? (There are some dotted line, but I'm not sure what does that mean.)

Then I made two measures:

 

Then I'm using filters like this:

For some reason this "Tarjouspvm_laskenta" (offer date) is blank and the other one seems to give some data.

Is there some problem still in relationships?

Thank you! :)))

[jussiwaisto]

It seems that this measuring get's stucked in offer confirmation date [Allekirjoituspvm]. 

I get out only those deals which have some date in the [Allekirjoituspvm] column. If that is empty, it doesnt get in to my list. 

[jussiwaisto]

Do I have to make to data tables?

[amitchandak]

what is the logic you want when the offer confirmation date is empty?

[jussiwaisto]

The logic is next:

If the deal confirmation is empty, it means it's just an offer. If there is date, it means that deal is confirmed.

[iplaygod]

Do you mean that you also want to count rows that have a blank value?

The measures were specifically made so that they only count those rows/customer ids where there IS a date value given in the column...

but if the rows have a blank value, how do you know which month or date they belong to?

you cant count all the blank rows for november (for example) since there the blank rows dont have any dates to group them by?

I dont think I understand what your data looks like.

[jussiwaisto]

Ok. let's take it again:

The situation was this. Every offer hav the offer date and deals have also deal confirmation date. If there is no deal, the confirmation date is empty. 

So I have 1 confirmed deal in february and 5 offers made in february. 

Customer	Offer date	Deal confirmation date
Cust1	1.1.2019
Cust2	2.1.2019
Cust3	3.1.2019
Cust4	4.1.2019	6.2.2019
Cust5	5.1.2019
Cust6	1.2.2019
Cust7	2.2.2019
Cust8	3.2.2019
Cust9	4.2.2019
Cust10	5.2.2019
Hitrate in february = 1 / 5 = 0,2 = 20% (cust4 / cust6+7+8+9+10)

[jussiwaisto]

I think I'm very close to the solution. 

I made two tests:

1. I made a relationship from the Allekirjoituspvm to Date and filtering worked well. 

2. Then I deleted that. (for testing)

3. Then I made a relationship from the Pvm to Date but filtering stopped working. So this can be one reason for the empty measure.

I also checked the date type from Allekirjoitspvm and Pvm, and both are same date -type. 

This is interesting...

[amitchandak]

Try something like this

IDs = ( 
VAR _Cuur_start = MIN('Date'[Date]) 
VAR _Curr_END = Max('Date'[ Date])
return 
calculate(countdistinct(Sales[ID]), not(isblank(Confirmation)) && Sales[Confirmation Date] >= _Cuur_start && Sales[Confirmation Date] <=  _Curr_END
,CrossFilter(Sales[offer Date],'Date'[Date] ) //in case you do not want to follow offer data 
))

[jussiwaisto]

Thanks, lets try!

Something went wrong in the syntax. (?) Did i wrote it right?

[jussiwaisto]

Then there is a filter which tells the status of an offer (0 is offer, 1 or bigger is deal). I added that filter to measures.

[amitchandak]

You need to use the userealtion to tell which relation to be used. Example

calculate(sum(Sales[Sales Amount]),Sales[Sales Date] >= _Cuur_start && Sales[Sales Date] <=  _Curr_END,USERELATIONSHIP(Sales[Sales Date],'Date'[Date Filer] )