Forum Discussion
Hitrate from two dates problem
what is the logic you want when the offer confirmation date is empty?
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.
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.
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.
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) 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 ))
