Hi @Synergetic,
Can you see if this if this meets your requirement and how this performs?
There are however some things to consider/choices to make, for example at the moment:
-
nYrs creates a table with all years in the date table, this might need to be restricted.
-
yTable creates a table from 0-10 to help determine the number of consecutive years
-
dTable creates a table with Donor ID’s, which now is limited to former/current staff
This is all brought together and in the end filtered down to only combinations, where the distinct count of receipt years equals the value from the number series in the yTable to help determine the max number of consecutive years.
.
Loyal Donors =
VAR nYrs = CALCULATETABLE( ALLNOBLANKROW( Dates[Year Offset] ))
VAR yTable = GENERATESERIES( 0, 10, 1)
VAR dTable =
CALCULATETABLE( VALUES( Donors[Donor ID] ),
Donors[Donor ID] IN VALUES('Former And Current Staff'[ID])
)
VAR nTable =
FILTER(
ADDCOLUMNS(
ADDCOLUMNS(
GENERATE( GENERATE( dTable, nYrs ), yTable ),
"@Offset", [Year Offset]
), "@Donor",
CALCULATE( DISTINCTCOUNT( 'All Recorded Donations'[Receipt Year] ),
FILTER( ALL( Dates ), Dates[Year Offset] >= [@Offset] -[Value] && Dates[Year Offset] < [@Offset]+1 )
)
), [Value] = [@Donor] &&
[Donor ID] IN VALUES( Donors[Donor ID] ) &&
[@Offset] IN VALUES( Dates[Year Offset] )
)
VAR myTable =
TOPN( 1,
ADDCOLUMNS(
nTable,
"@Rank", RANKX( FILTER( nTable, [@Offset] = EARLIER( [@Offset] )), [Value], , ASC, Dense)
), [@Rank], ASC
)
VAR Result = MAXX( myTable, [@Donor] )
RETURN Result
.
Also created a What if - parameter called: Consecutive Num of Years comprised of a list {3, 5, 10} that can be used to identify/count the number of donors that meet that number when place in a table or Slicer.
Loyal Donors N consecutive years =
VAR nYrs = CALCULATETABLE( ALLNOBLANKROW( Dates[Year Offset] ))
VAR yTable = GENERATESERIES( 0, 10, 1)
VAR dTable =
CALCULATETABLE( VALUES( Donors[Donor ID] ),
Donors[Donor ID] IN VALUES('Former And Current Staff'[ID])
)
VAR nTable =
FILTER(
ADDCOLUMNS(
ADDCOLUMNS(
GENERATE( GENERATE( dTable, nYrs ), yTable ),
"@Offset", [Year Offset]
), "@Donor",
CALCULATE( DISTINCTCOUNT( 'All Recorded Donations'[Receipt Year] ),
FILTER( ALL( Dates ), Dates[Year Offset] >= [@Offset] -[Value] && Dates[Year Offset] < [@Offset]+1 )
)
), [Value] = [@Donor] &&
[@Donor] IN VALUES( 'Consecutive Num of Years'[Consecutive Years] ) &&
[Donor ID] IN VALUES( Donors[Donor ID] ) &&
[@Offset] IN VALUES( Dates[Year Offset] )
)
VAR myTable =
TOPN( 1,
ADDCOLUMNS(
nTable,
"@Rank", RANKX( FILTER( nTable, [@Offset] = EARLIER( [@Offset] )), [Value], , ASC, Dense)
), [@Rank], ASC
)
VAR Result = COUNTROWS( myTable )
RETURN Result
.
One thing I haven’t been able to fix is the flag measure… but I expect that will produce the correct result once you’ve updated the Dates table to include all dates in 2020 (now partialy covered).
Flag Donors =
VAR dTable =
EXCEPT(
CALCULATETABLE( VALUES( 'All Recorded Donations'[Donor ID] ), 'All Recorded Donations'[Donor ID] IN VALUES( 'Former And Current Staff'[ID] ), DATEADD( Dates[Date], -1, YEAR )),
CALCULATETABLE( VALUES( 'All Recorded Donations'[Donor ID] ), 'All Recorded Donations'[Donor ID] IN VALUES( 'Former And Current Staff'[ID] ))
)
VAR vTable =
FILTER(
ADDCOLUMNS( dTable,
"@pYr", [Loyal Donors LY]
),
[@pYr] >= 3 &&
[Donor ID] IN VALUES( Donors[Donor ID] )
)
RETURN
COUNTROWS( vTable )
.
.
Here’s your sample file eDNA - Donor loyalty.pbix (813.7 KB)
I hope this is helpful.
