Forum Discussion

LindseyJ's avatar
LindseyJ
Frequent Visitor
6 years ago

Calculate Returning Visits Based on a Dynamic Date Range

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 g...
Need Help
MFelix's avatar
MFelix
Icon for Super User rankSuper User
6 years ago

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's avatar
LindseyJ
Frequent Visitor
6 years ago

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.