Need Help Identifying Repeat Clients Over Dynamic Time Period

[apmulhearn]

Hello,

I am trying to calculate the percentage of new bookers vs repeat bookers.

Our variable driving date is the "First Payment Date."  So I need the calculation to adjust as the First Payment Date Range changes.

The way we know a guest has travelled before is based on their End Date.

The question I want to answer is, for All First Payments Received between X/XX/XXXX - X/XX/XXXX, how many clients had already completed AT LEAST one trip?

Using this small sample, if the First Payment Date Range were 9/1/2017 - 9/1/2019, only the rows in blue would be eligible, and my answer would be 1. Client ID 98076 MADE a booking in my queried range and completed 2 already.  

So I guess I need a DISTINCTCOUNT of Client IDs which, over a given FIRST PAYMENT DATE range, have End Dates in other rows which are PRIOR TO the dates in the FIRST PAYMENT DATE range.

ClientID	End Date	Trip Code	Status	First Payment Date
12345	1/1/2017	XA908	Completed	3/17/2016
12345	2/18/2021	BA807	Completed	7/1/2020
12345	5/11/2023	XA908	Booked	6/1/2021
12345	4/1/2015	SA306	Completed	3/1/2014
98076	6/10/2018	FA432	Completed	9/23/2017
98076	4/3/2019	BA999	Completed	7/17/2018
98076	4/3/2020	BA346	Completed	6/7/2019
4356	2/20/2020	SA456	Completed	8/3/2019

[v-angzheng-msft · Accepted solution]

Hi, @apmulhearn 

First create a calendar table

Calendar = 
var _min=MIN('Table'[First Payment Date])
var _max=MAX('Table'[First Payment Date])
return
CALENDAR(_min,_max)

then create two measures.

_isResult = 
var _payInRange=MAX('Table'[First Payment Date])in DATESBETWEEN('Calendar'[Date],MIN('Calendar'[Date]),MAX('Calendar'[Date]))
var _endInRange=MAX('Table'[End Date])in DATESBETWEEN('Calendar'[Date],MIN('Calendar'[Date]),MAX('Calendar'[Date]))
var _if=IF(_payInRange&&_endInRange,1,0)
return
    _if

_distinctcountID = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[ClientID] ),
    FILTER ( ALL ( 'Table' ), [_isResult] = 1 )
)

so we can show it with a card visual.

Result:

Please refer to the attachment below for details

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.