Count ex-members (TIN) based on MembershipEndDate

[tzaveri]

Looking to get count of ex-members (using their TIN in TIN column) from last 24 months based on MembershipEndDate.

[Anonymous · Accepted solution]

Hi @tzaveri,

Here is the measure formula that can get rolling 2 year records and get distinct member count, you can try it if it suitable for your requirement.

formula =
VAR currDate =
    MAX ( Table1[MembershipEndDate] )
RETURN
    CALCULATE (
        COUNTROWS ( VALUES ( Table1[TIN] ) ),
        FILTER (
            ALLSELECTED ( Table1 ),
            [MembershipEndDate]
                >= DATE ( YEAR ( currDate ) - 2, MONTH ( currDate ), DAY ( currDate )
                    && [MembershipEndDate] <= currDate )
        )
    )

Regards,

Xiaoxin Sheng