topic Re: Count the number of years a contact has donated in DAX Commands and Tips

Count the number of years a contact has donated

sflaming — Thu, 13 Jul 2023 23:37:41 GMT

I am trying to create a measure which will count the number of years that a contact has given a donation.

For example, using the sample data below (which is linked by Contact ID, and the date recieved is linked to a calendar dimension date table), I would like the output of the measure/column to be 3 for Sarah John (she contributed in 3 different years), 4 for Tom Kim, etc.

Any thoughts?

Contact ID	Display Name
45	Sarah John
102	Tom Kim
845	Rebecca Firsk

ContactID	Display Name	Date Received	Soft Credit Amount
45	Sarah John	11/15/2020 0:00	150
45	Sarah John	12/20/2020 0:00	60
102	Tom Kim	12/24/2020 0:00	62.5
102	Tom Kim	2/5/2021 0:00	20
845	Rebecca Firsk	3/6/2021 0:00	25
845	Rebecca Firsk	5/18/2021 0:00	8.34
45	Sarah John	10/14/2021 0:00	20.84
845	Rebecca Firsk	3/4/2022 0:00	20
845	Rebecca Firsk	3/7/2022 0:00	50
45	Sarah John	6/30/2022 0:00	100
102	Tom Kim	8/23/2022 0:00	25
845	Rebecca Firsk	12/28/2022 0:00	150
102	Tom Kim	4/20/2023 0:00	28

Re: Count the number of years a contact has donated

barritown — Fri, 14 Jul 2023 09:38:02 GMT

Hi sflaming,

If your Calendar table has a bi-directional cross filtering with the fact table and you have the [Year] column in it:

the measure may be as simple as that:

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

Re: Count the number of years a contact has donated

sflaming — Fri, 14 Jul 2023 13:45:36 GMT

barritown thank you! When I try that, I am getting a count of all years. Is that because I have a many to one relationship here? I made the relationship bidirectional. Here's my model:

Re: Count the number of years a contact has donated

barritown — Fri, 14 Jul 2023 14:27:03 GMT

sflaming, no, one-to-many shouldn't spoil it. There is something else, I guess.

You can try such an alternative, which is heavier and slower (and ignores the fact that you have a calendar table) but may work:

In plain text:

Measure 2 = VAR _tbl = SUMMARIZE ( ADDCOLUMNS ( Donations, "Year", YEAR ( [Date Received] ) ), [ContactID], [Year] ) RETURN COUNTROWS ( _tbl )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

Re: Count the number of years a contact has donated

sflaming — Fri, 14 Jul 2023 17:50:33 GMT

barritown I ended up adding a column to my calendar dimension table called "Year", and then created a measure

Number Of Years = DISTINCTCOUNT(DimDate[Year]) that works!

Not sure why I couldn't use the 'Year' from the date hierarchy here, but happy to have it working now!