- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Help with DAX: Need count of how many years a donor has made at least one donation
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Can anybody else help a rookie ?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
Can't you put a "count" behaviour for the donor_name column by right clicking on it ?
Igna
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - January 2025
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
Subject | Author | Posted | |
---|---|---|---|
11-29-2024 02:03 AM | |||
07-13-2023 04:37 PM | |||
10-14-2024 02:53 AM | |||
07-14-2023 07:20 AM | |||
10-18-2024 09:06 AM |
User | Count |
---|---|
98 | |
69 | |
46 | |
39 | |
33 |
User | Count |
---|---|
157 | |
101 | |
60 | |
42 | |
40 |