Forum Discussion
Helper ICalculate donor loyalty
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
1 Reply
- stretcharm
Memorable Member
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/
