Forum Discussion

tktmastr's avatar
tktmastr
Helper I
5 years ago
Solved

Calculating Consecutive Years Active

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 shou...
  • mahoneypat's avatar
    5 years ago

    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