aggregating rows based on date difference

[Anonymous]

Hello,

I have a large database (900K records with >30 columns), but a simplified description:

DonorID  Area  Date         Donation

037501   A     2020-12-03   20
522001   A     2022-05-12   45
987301   B     2020-11-30   5
037501   A     2021-02-01   10

...
(etcetera)

Now I want to examine how many donors in area A have made a donation in a specific year, and also made at least 1 previous donation within the last 12 months before that donation.

So in the very simplified dataset above:

2023: 0
2022: 0
2021: 1 (DonorID 037501)
2020: 0

But how to filter/aggregate these specific grof donors? My idea was to use a self-join in combination with additional selections, but I don't know if a self-join on such a large database (with data over 30 years) is a good idea.

Any ideas how to tackle this?

[pratyashasamal]

Hi @Anonymous ,
Creating User defined aggregation table is very helpful for large dataset .
You can refer to this link for information about aggregation table :-

https://learn.microsoft.com/en-us/power-bi/transform-model/aggregations-advanced
Hope this was helpful.
Thanks,
Pratyasha Samal
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

[Anonymous]

Hi, thanks for the link with info about creating aggregations. I have read the page in detail. Maybe this technique could provide a solution for this particular case, but how do I specificy that aggregation should be dependent on a specific time interval between distinct records?