Visit Frequency

[manideep547]

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 
 

[Ashish_Mathur]

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?

[v-lili6-msft · Accepted solution]

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

[Anonymous]

Hi,

Assuming you have the following table named "Visits"

ID	Date
1	01 January 2019
1	01 February 2019
2	01 May 2019
3	01 January 2019
3	01 March 2019
3	01 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]

@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