Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I need to calculate the loyalty of donors. So when a donor gives a donation for the first time he is active for that year. When he gives the next year again he is still active and so on. When he is not giving in a year he becomes inactive. When he gives the year thereafter again he starts to be active again.
My data is like:
DonorID | Date | Amount |
100 | 1-2-2016 | 100 |
100 | 1-2-2017 | 100 |
100 | 1-2-2018 | 100 |
101 | 1-1-2015 | 100 |
101 | 1-1-2017 | 100 |
101 | 1-1-2018 | 100 |
The outcome should be:
Inflow | active Year 1 | active year 2 | active year 3 |
2015 | 1 | 0 | 0 |
2016 | 1 | 1 | 1 |
2017 | 1 | 1 | 0 |
2018 | 0 | 0 | 0 |
Thanks in advance!
Regards,
Jarno
If you create a date dimension table and then join your Donor data date. You can then summarise by date fields such as year.
You can create a date dim in either Dax or M language or from a db table. Here are some options
http://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-1-calendar-columns
https://www.mssqltips.com/sqlservertip/4857/creating-a-date-dimension-table-in-power-bi/
https://www.sqlbi.com/articles/reference-date-table-in-dax-and-power-bi/
Example of doing time intelligence
https://www.sqlbi.com/articles/time-intelligence-in-power-bi-desktop/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
77 | |
68 | |
54 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |