Forum Discussion
Hitrate from two dates problem
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 ))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...
Thanks, lets try!
Something went wrong in the syntax. (?) Did i wrote it right?
