Forum Discussion
Calculate Returning Visits Based on a Dynamic Date Range
Super UserHi LindseyJ ,
Try the following measures:
Count =
CALCULATE (
SUM ( Visits[#_OF_VISITS] );
FILTER (
ALL ( Visits[DATE] );
Visits[DATE] <= MAX ( Dates[DATE] )
&& Visits[DATE] >= DATEADD ( Dates[DATE]; -12; MONTH )
)
)
ReturningCount =
VAR temp_table =
SUMMARIZE ( Visits; Visits[CLIENT_ID]; "SSS"; SUM ( Visits[#_OF_VISITS] ) - 1 )
RETURN
CALCULATE (
COUNTROWS ( FILTER ( temp_table; [SSS] > 0 ) );
FILTER (
ALL ( Visits[DATE] );
Visits[DATE] <= MAX ( Dates[DATE] )
&& Visits[DATE] >= DATEADD ( Dates[DATE]; -12; MONTH )
)
)
this can be made dinamic using an what if parameter, please tell me if the calculations is correct and then I can change it to dinamic selecting 12 months, 6 months or whatever number you need.
Hi MFelix, Thanks for your response!
Since [Count] is a sum of all visits within the time period and [ReturningCount] subtracts 1 visit from each client, are these intended to be combined into one measure to calculate the Sum of Return Visits?:
[Sum of Return Visits] = [Count] - [ReturningCount]
Is there a way we can get the [Sum of Return Visits] to aggregate by month (for the last 12 months)? Currently when I place these measures into a visual, they aggregate at the CLIENT_ID level but not at the DATE level (apologies if this was meant to be part of the next step you mentioned to make it dynamic). I need the 1st (earliest) visit to be subtracted from each client in the month it occurred based on the 12 month date range. For example, if a client had 1 visit in February 2020 and 2 visits in January 2020 for a total of 3 visits, and February 2020 was the date slicer selection, [Sum of Return Visits] in February 2020 would be 1, and January 2020 would be 1 (since January was the earliest visit month for this client within the 12 months prior, 1 visit would be subtracted) for a total of 2 Return Visits:
Thanks for your help!
Lindsey
- MFelix
Hi LindseyJ ,
Sorry for making the questions but I just want to understand the way you need the calculations.
Let's pickup customers A to D. I can see that the sum of visits by month are 91 (total) for this 4 customers and in april the total is 62 for past 12 months I have for those customers the following count of visits:
I have made changes to the measures:
ReturningCount = VAR temp_table = SUMMARIZE ( Visits, Visits[CLIENT_ID], "@Visits_Total", SUM ( Visits[#_OF_VISITS] ) - 1 ) RETURN CALCULATE ( COUNTROWS ( FILTER ( temp_table, [@Visits_Total] > 0 ) ), DATESINPERIOD(Dates[DATE], MAX(Dates[DATE]), -12, MONTH) ) Count = CALCULATE ( SUM ( Visits[#_OF_VISITS] ), DATESINPERIOD ( Dates[DATE] , MAX ( Dates[DATE] ), -12 , MONTH ) )Based on the values for the 4 customer can you please tell me what is the result is correct?
I have also made the date table connected with the visits table.
Can you check the result for those 4 customers and tell me if returnin and count is correct?
The dinamic part will be done by making the change of the -12 to a what if parameter but it's a simple change let's first align the number for this fix 12 months from selected date.
Check PBIX file attach.
Hi MFelix, If the data is filtered to ABCD and April 2020 is the selected date, this would be the desired result (58 return visits and 3 returning clients):
If the date range was fixed, I would add 2 columns to the Visits table:
1) One column to rank the dates for each client id:
Date_Rank =RANKX ( FILTER (Visits, Visits[CLIENT_ID] = EARLIER ( Visits[CLIENT_ID] ) ),
Visits[DATE], , ASC )And 2) another column to calculate the # of return visits for each row based on the Date_Rank, and then use this column to create the measures I needed:Return_Visits =IF ( Visits[Date_Rank] = 1,
Visits[#_OF_VISITS] - 1,
Visits[#_OF_VISITS] )Since I want to filter the Visits table to only look at the last 12 months from the date selected (not the full 24 months) and calculate return visits based on those 12 filtered months, this specific method doesn't work. But if we could get some equivalent to this calculation, adjusting for the different date ranges, that would be awesome. Here is a link to a new copy of the .pbix file with the desired results for ABCD and the calculated columns I mentioned above added for reference.