Help with DAX: Need count of how many years a donor has made at least one donation

[TK-PRES]

Source data fileds from "SalesReceipts"

Donor_Name ... DonationDateYear ... Amount

Specific case is over 5 years of data with each sales receipt in a seperate row

Example Expected result

Years    NumberOfDonors

5          52

4          66

3          99

2          133

1          217

I was able to create a visual that shows the following, but cannot create the expected table shown above

Donor_Name     2018  2019  2020  2021  2022   Total

Name 1              $50              $100                       $150

Name 2                       $75              $75     $75     $225

........

Thanks for your help!      

[TK-PRES]

Can anybody else help a rookie ?

[Igna]

Hello,

I would create a calculated column to identify the donors :

HasDonation = IF(SUMX(FILTER(SalesReceipts, SalesReceipts[DonationDateYear] = EARLIER(SalesReceipts[DonationDateYear])), 1) > 0, 1, 0)

Then a calculated column that count the number of unique years each donor has made donations :

YearsWithDonations = COUNTROWS(FILTER(SalesReceipts, SalesReceipts[Donor_Name] = EARLIER(SalesReceipts[Donor_Name]) && SalesReceipts[HasDonation] = 1))

With that you can create a table with Donor_Name and YearsWithDonations.

Hope it helps.

Igna

[TK-PRES]

Thank you Igna. I appreciate your help. I tried your suggestion which results in a table with donor_names in the rows and the total count of the number of donations, not the total number of years a donor donated. In reviewing the suggested calculated columns, "HasDonation" results in a "1" for each line since the database only includes actual donor donations. The "Yearswith Donation" adds up all those "1".

Once the above is corrected, there's another step to provide a summary table (i.e. not by donor_name)

Example Expected result is shown below where YEARS is the number of years donated. In my case I am looking at the last 5 years.

 Years    NumberOfDonors

5                52

4                66

3                99

2                133

1                217

[Igna]

Hi,

Can't you put a "count" behaviour for the donor_name column by right clicking on it ?

Igna

[TK-PRES]

That does not work. I will restate that the calculated value of YearsWithDonations for each DONOR sums ALL the donations made, not the number of years. Thanks again for trying.