Forum Discussion

manideep547's avatar
manideep547
Icon for Helper III rankHelper III
6 years ago
Solved

Visit Frequency

Hi  Table A  Id      Date          1        1/1/2019 1        1/02/2019 2        1/5/2019 3         1/1/2019 3         1/03/2019 3         01/06/2019 count of the customers visits frequency ...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    hi manideep547 

    For your case, you could try this way as below:

    Note: if your sample data is wrong, id 3 doesn't have the transaction once every 3 months

    Step1:

    Add a calculate column that Visit Frequency as below:

    Visit Frequency = 
var _id='Table'[ID] 
var _date='Table'[Date]
var _nextdate=CALCULATE(MIN('Table'[Date]),FILTER('Table','Table'[ID]=_id && 'Table'[Date]>_date))
var _Frequency= DATEDIFF(_date,_nextdate,MONTH)
return
"Once Every" & _Frequency & "Months"

    Step2:

    Drag Visit Frequency column in a visual and set visual level filter to filter 'Once EveryMonths' and drag ID column into visual too.

     

    and here is sample pbix file, please try it.

     

    Regards,

    Lin

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
6 years ago

Hi,

What result do you expact to see if after 01/06/2019, Customer ID 3 starts visiting one every week?  Take a reaslistic example and show the expected result.  Also, will the Date column always be the first date of every month?  Furthermore, how many visits intances per customer should be studied to bucket the customer into relevant categories.  So suppose Customer 3 appeared once every 3 months for the first 4 instances but thereafter made a random number of visits.  What result do you want then?