Forum Discussion
Calculating Consecutive Years Active
- 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
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