Number of donors by value, through time

[PBI-Bro]

We need to create a table visualisation to display the following values by year and month (extendable to quarter and week):

1. Number of active donors 

2. The Number of active donors split by their category. This category is based on the amount donated over the last 12 months.

Donor category is defined as: 

0-100 -> low

101 - 500 --> middle

500+ --> high

The end result should look like this:

Year	Quarter	Month	Active Donors	Low	Middle	High
2023	4	October	280	180	80	20
2023	4	November	310	200	100	10
2023	4	December	320	190	80	50
2024	1	January	120	90	20	10

For example:  In October 2023 received donations from 180 donors who are categorized as "low" because their donations in the previous 12 months amounted to less than USD100.

My model has the tables transaction, donor and calendar. Calendar hat a relationsihp to transaction and is marked as calendar table.

I can calculate the number of active donors with this measure:

Active Donors = 
VAR Result =
    CALCULATE (
        COUNT('Donor'[DonorID]),
        FILTER(
            'Transaction',
             'Transaction'[Status] = "Booked"
             )
    )
RETURN Result

I am having trouble figuring out how to write measures to calculate the categories. I can calculate them for a static point in time but not in a way that they take the date categories into account.  What should I look into? Thanks fory any help! 

[v-heq-msft]

Hi @PBI-Bro ,
Based on your description, you are looking to create a table visualization that displays the number of active donors along with their categories by time period. To calculate the donor categories dynamically based on the amount donated over the last 12 months, you'll need to write DAX measures that take into account the time intelligence functions.

Here's another expression based on the MEASUR you created, you can try this MEASURE to see if it improves your computation speed. The rest of the levels of MEASURE you can achieve by changing the scope of the restrictions

High Category Donors = 
CALCULATE(
    COUNTROWS(
        VALUES('Donor'[DonorID])
    ),
    FILTER(
        SUMMARIZE(
            'Transaction',
            'Donor'[DonorID],
            "TotalDonation", CALCULATE(SUM('Transaction'[Amount]), 'Transaction'[Status] = "Booked")
        ),
        [TotalDonation] > 500
    ),
    DATESBETWEEN('Calendar'[Date], DATEADD(LASTDATE('Calendar'[Date]), -1, YEAR), LASTDATE('Calendar'[Date]))
)

Best regards

Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
 

[PBI-Bro · Accepted solution]

I managed to sort out a calculation like this:

High Donors = 

CALCULATE (
    [Active Donors],
    FILTER (
        ALL('Donor'),
        CALCULATE (
            SUM('Transaction'[Amount]),
            DATESINPERIOD(
                'Calendar'[Date],
                LASTDATE('Calendar'[Date]),
                -12,
                MONTH
            )
        ) >= 500
    )
)

Would you say this is the right approach?
When I add the three categories as measures to my table it is taking a long time to calculate. Is there a better way to achieve this?