Calculating Consecutive Years Active

[tktmastr]

Hi,

I am trying to figure out how to calculate the # of years a donor has been active.... if they have skipped a year, I want it to start over again.

i.e. in the table below, customer #2 should only be active for one year, because they did not donate in 2019. I can't figure how how to do this if they skipped a year or more. 

Thanks!

Data Table
Customer ID	Fiscal Year
1	2018
1	2019
1	2020
2	2017
2	2018
2	2020
3	2020
4	2017
4	2018
4	2019
4	2020
Expected Result
Customer ID	Consecutive Donor Years
1	3
2	1
3	1
4	4

[mahoneypat · Accepted solution]

Please try this measure expression in a table visual with your Fiscal Year column, replace Donors with your actual table name.

Consec Yrs =
VAR vMaxYear =
    MAX ( Donors[Fiscal Year] )
VAR vThisCustomer =
    MIN ( Donors[Customer ID] )
VAR vLatestNotDonation =
    MAXX (
        FILTER (
            ALL ( Donors[Fiscal Year] ),
            ISBLANK (
                CALCULATE (
                    COUNTROWS ( Donors ),
                    Donors[Customer ID] = vThisCustomer
                )
            )
        ),
        Donors[Fiscal Year]
    )
VAR vLatestIfBlank =
    IF (
        ISBLANK ( vLatestNotDonation ),
        MINX (
            ALL ( Donors[Fiscal Year] ),
            Donors[Fiscal Year]
        ) - 1,
        vLatestNotDonation
    )
RETURN
    IF (
        vMaxYear = 2020,
        vMaxYear - vLatestIfBlank
    )

Pat