Forum Discussion

apmulhearn's avatar
apmulhearn
Helper III
5 years ago
Solved

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 ...
Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    5 years ago

    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.

v-angzheng-msft's avatar
v-angzheng-msft
Community Support
5 years ago

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.