Incorrect results using customer churning examples

I am using the customer churning examples to calculate, in my case lost donors for a fund raiser.
However, my results are nonsense.
I have a total of 4000 donors, yet the DAX formula is telling me I am losing over 4000 donors per month!

I have checked it against the demo model and it is correct (as far as I can tell). I have uploaded the model to see if there is something I have missed.

Donations.pbix (599.1 KB)

Hi Synergetic,

I have had a look at your model and calculations and there are a couple of points for you to consider.

Currently your Fact Table ( All Recorded Donation Table) has 4460 distinct values for ID however your dimension (Former And Current Staff) has 1077 distinct values for Donor ID.

This Doesn’t seem correct as the dimension should contain All distinct values. So something you should have a look at.

In your current model the calculation you have written is looking at the 4460 ID values and is therefore returning the correct result.

I can see in your distinct count of donors you have done this over AllRecordedDonationsTable[DonorID] which returns the 1678.

I amended the formula to look at this value and think this is returning what you are after.

If you need any further explanation or help do not hesitate to shout. :slight_smile:

Donations _HA.pbix (601.0 KB)


That does seem more reasonable now.

Normally a dimension would have distinct values, but in this case some staff leave and return. Hence they appear in the table twice. I removed those personal details from the upload.

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)