Forum Discussion

manideep547's avatar
manideep547
Helper 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 like Once Every 3 Months / Once A Month / Once A Week / More Than Once A Week from the first transaction of the particular customer. 
Once Every 3 Months =1(id 3)  
id 3 has the transaction once every 3 months  starting from first Transactions of that customer 
Once A Month (count)=1(id1) 
Id 1 has the transaction Once A Month  starting from first Transactions  of that customer 
 

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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi,

     

    Assuming you have the following table named "Visits"

     

     

    IDDate
    101 January 2019
    101 February 2019
    201 May 2019
    301 January 2019
    301 March 2019
    301 June 2019

     

    You can get the results by the following steps. You may modify as you wish.

     

    Step 1: Add a Calculated Table named "Customers"

     

    Customers = ALL(Visits[ID])

     

    Step 2: Add the following calculated columns to the newly created "Customers" table.

     

    FirstDate =
MINX ( FILTER ( Visits, Visits[ID] = Customers[ID] ), Visits[Date] )

     

    LastDate =
MAXX ( FILTER ( Visits, Visits[ID] = Customers[ID] ), Visits[Date] )

     

    NumberOfVisits =
COUNTROWS ( FILTER ( Visits, Visits[ID] = Customers[ID] ) )

     

    DaysInRange =
DATEDIFF ( Customers[FirstDate], Customers[LastDate], DAY )

     

    Frequency =
DIVIDE ( Customers[DaysInRange], Customers[NumberOfVisits], 0 )

     

    FrequencyOfVisit =
IF (
    Customers[Frequency] < 7,
    "More Than Once In A Week",
    IF (
        Customers[Frequency] < 30,
        "Once In A Month",
        IF ( Customers[Frequency] <= 90, "Once In Every 3 Months", "Sporadic" )
    )
)

     

    Then you may use the "FrequencyOfVisit" and "CustomerID" columns in your report visualizations. 

    • manideep547's avatar
      manideep547
      Helper III
      6 years ago

      Anonymous I have four Visits tables I have to display that. So is there any way to display the count from 4 tables without creating again 4 tables because my file is almost 1 GB 

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    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
    Super 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?