Measure to calculate 'for clients signed up 3 months ago, how many made payment in next two months'

[KoalaPBI]

Hi community,

In the following dataset,  I want to create a measure to calculate :  'for clients signed up 3 months ago, how many made payment in next two months'. Then calculate the monthly average over these next two months.

for instance, in following snippet if 22 clients signed up 3 months ago( in Apr), out of 22, 11 made a payment in May & 15 made a payment in June, then monthly average over May & June:  (11+15)/(22x2)=59%

Here is my sample pbi file:

https://1drv.ms/u/s!Ag919_pO_UKrghhIKgRk6OM2HdLS?e=HB1m2N

Many thanks for your help!

[Jihwan_Kim · Accepted solution]

Hi,

Please try using the below measure.

client count within two months ratio: =
VAR _twomonthlaterendofmonth =
    EOMONTH ( MAX ( Datedim[Date] ), 2 )
VAR _customerlist =
    FILTER (
        SUMMARIZE ( 'Table', 'Table'[Client_id], 'Table'[paid_date] ),
        'Table'[paid_date] <= _twomonthlaterendofmonth
    )
RETURN
    DIVIDE ( COUNTROWS ( _customerlist ), [Signups] * 2 )