Calculate donor loyalty

[JarnoVisser]

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 

[stretcharm]

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/