Loyalty tracking

Power BI Data Model
1

Hi all,
In a previous question I had an issue with the incorrect donor numbers on this file. That was resolved.

The next part of my problem is that I now need to track donor loyalty.

I want to discover which donors have donated every year for the past 3 and past 5 years.

Then what I want to discover is out of those donors, who are the subset of those donors who are staff or former staff (the dimension table).

Can someone point me to a resource that will assist me in identifying the those donors that have been loyal?

Thanks

Donations _HA.pbix (601.0 KB)

2

@Synergetic,

There are multiple ways you can approach this.

For resources select Search (magnifying glass), Options, Enterprise DNA Online (banner) and use keywords like: virtual tables, countrows, filter, values, calculate and intersect (that could be an alternative).

So I added a YearOffset to your Dates table, this will allow you to select previous years more easily.

Donor in last 3 years = 
VAR myTable =
FILTER(
    ADDCOLUMNS(    
        ADDCOLUMNS(
            VALUES ( AllRecordedDonationsTable[DonorID] )|
            "Y3" | CALCULATE( COUNTROWS( AllRecordedDonationsTable )| FILTER ( ALL ( Dates )| Dates[YearOffset] = -3 ))|
            "Y2" | CALCULATE( COUNTROWS( AllRecordedDonationsTable )| FILTER ( ALL ( Dates )| Dates[YearOffset] = -2 ))|
            "Y1" | CALCULATE( COUNTROWS( AllRecordedDonationsTable )| FILTER ( ALL ( Dates )| Dates[YearOffset] = -1 )))|
        "Last3Y" | IF( [Y3] >0 && [Y2] >0 && [Y1]>0 | TRUE() | FALSE() ))|
    [Last3Y] = TRUE()
)
VAR Result = COUNTROWS( myTable )

RETURN Result

And for those donors who are staff or former staff

Donor in last 3 years Former/Staff = 
VAR myTable =
FILTER(
    ADDCOLUMNS(    
        ADDCOLUMNS(
            VALUES ( AllRecordedDonationsTable[DonorID] )|
            "Y3" | CALCULATE( COUNTROWS( AllRecordedDonationsTable )| FILTER ( ALL ( Dates )| Dates[YearOffset] = -3 ))|
            "Y2" | CALCULATE( COUNTROWS( AllRecordedDonationsTable )| FILTER ( ALL ( Dates )| Dates[YearOffset] = -2 ))|
            "Y1" | CALCULATE( COUNTROWS( AllRecordedDonationsTable )| FILTER ( ALL ( Dates )| Dates[YearOffset] = -1 )))|
        "Last3Y" | IF( [Y3] >0 && [Y2] >0 && [Y1]>0 | TRUE() | FALSE() )|
        "Staff" | IF( [DonorID] IN VALUES(Former_and_Current_Staff_Table[ID]) | TRUE() | FALSE() ))|
    [Last3Y] = TRUE() &&
    [Staff] = TRUE()
)
VAR Result = COUNTROWS( myTable )

RETURN Result

.
With this result:

image
image.png800Ă—712 12.5 KB

I hope this was helpful. Here’s my sample file:
e-DNA - Loyalty tracking.pbix (596.0 KB)

4 Likes
3

Hi @Synergetic, we’ve noticed that no response has been received from you within the last 24 hrs. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

4

Hi,
This is close, but what I need to track is that they have donated for

  • every year for the last 3 years
  • every year for the last 5 years
  • every year for the last 10 years

Not just once in the last 3 years

5

Hi @Synergetic,

The measures provided in the previous post count the number of DonorID’s that have donated the last 3 or 5 years in a row. Placed in a area chart with the Year on the axis, you’ll get this:

image
image.png1184Ă—407 35.1 KB

Please let me know if this meets your requirement and/or if you have any further questions.
e-DNA - Loyalty tracking.pbix (596.9 KB)

6

Thanks @Melissa
I think I was confused by the name of the measure. Maybe it should have been “Donor for the last 3 years”.

I will validate this against the data but if this is correct then this is exactly what I needed.

7

Okay thanks.

Please note that these measures now have a fixed yearly offset - so they are always measuring a whole calendar year over a whole calendar year.
If you want to track it on a weekly or daily level, that will be easy enough to do with some slight changes to this pattern.

Let me know what you find.

8

Thanks Melissa
I have verified with a few of the IDs and this is correct.
We want to get ensure that the donor has donated at least once within a calendar year.
I am just trying to find one that has donated several times in a calendar year to check that they are not counted twice.