Forum Discussion

jussiwaisto's avatar
jussiwaisto
Helper I
6 years ago

Hitrate from two dates problem

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.

 

CustomerOffer dateDeal confirmation date
Cust11.1.2019 
Cust22.1.2019 
Cust33.1.2019 
Cust44.1.20196.2.2019
Cust55.1.2019 
Cust61.2.2019 
Cust72.2.2019 
Cust83.2.2019 
Cust94.2.2019 
Cust105.2.2019 
   
Hitrate in february = 1 / 5 = 0,2 = 20% (cust4 / cust6+7+8+9+10)

 

How do I solve this problem?

 

Need Help
Show and Tell

13 Replies

  • iplaygod's avatar
    iplaygod
    Resolver I
    6 years ago

    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's avatar
      jussiwaisto
      Helper I
      6 years ago

      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! :)))

       

      • amitchandak's avatar
        amitchandak
        Super User
        6 years ago

        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] )