Forum Discussion
Need Help Identifying Repeat Clients Over Dynamic Time Period
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 |
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.
1 Reply
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.