Calculate Returning Visits Based on a Dynamic Date Range

[LindseyJ]

Hello Power BI Experts! I have a table with Client IDs, Dates (by month, going back 24 months), Number of Website Visits in each month and an Index (ordered by ascending client id and date).

My goal is to create a measure that calculates the Sum of Return Visits in a given month, and a second measure counting the Number of Clients Making a Return Visit, with a look back window of 12 months. A Return Visit means a client has visited the website at least once in the past 12 months and has returned for a 2nd/3rd/etc. time. So for the selected month, removing any visit that is a client's 1st visit in the past 12 months and summing/counting the rest. (Note - a client can have a 1st time visit and a return visit in the same month). I plan to use these measures in number card visuals, column chart visuals (over time) and to create additional % to total measures.

The given month is determined by a date slicer from a disconnected date table, from which users of this report will be able to select any month within the past 12 months.

For example, based on the sample data below, if February 2020 was selected in the date slicer, based on the prior 12 months the Sum of Return Visits = 4 and the Number of Clients Making a Return Visit = 2:

Here is a link to a .pbix file with a larger sample data set: Return Visit Sample Data 

If the requirement was a static 12 month period, I would just create a calculated column for return visits (subtracting 1 visit from each client's lowest ranked month) and create my measures from that, but I am having trouble finding a solution with a dynamic date range.

Any help would be greatly appreciated! Thank you!!

[MFelix]

Hi @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.

[LindseyJ]

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.

[LindseyJ]

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.