new donors and repeat donors in line chart based on year selection

[powerbiexpert22]

i need to create line chart which should show two measures - "new donors" and "repeat donors" by months ( month should be on x axis) . months should display default for year 2024 , user can select year and it should display months on x axis based on selected year by user. i need help on DAX for these two measures

logic for new donors:  if donor has not donated in earlier months starting from Dec 2023 until current month then it is a new Donor,  example for January 2024 based on below data, new donors should be 2 and 3 because they did not donate in Dec 2023 . 

logic for repeated donors: if donor has donated in earlier months starting from Dec 2023 until current month then it is a repeated Donor,  example for January 2024 based on below data, repeated donors should be 1 and 4 because they donated in Dec 2023 

donation_id	alumni_id	donation_date_key	donation_amount
1	1	20231216	10
2	1	20240101	5
3	2	20240101	20
4	2	20240201	35
5	3	20240110	15
6	4	20231201	20
7	4	20240115	25
8	5	20240201	40
9	6	20240301	35
10	6	20240401	10
11	7	20240501	10

[Ashish_Mathur]

Hi,

PBI file attached.

[ThxAlot]

Easy enough,

For fun only, to showcase power of Excel formulas,

[danextian · Accepted solution]

Hi @powerbiexpert22 

Use a separate dates table with a one-to-many single direction relationship to your fact table on donation date (donation date key converted to date)

and create these measures

New Donors = 
VAR _pastDonors =
    CALCULATETABLE (
        VALUES ( SampleData[alumni_id] ),
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] < MIN ( 'Calendar'[Date] ) )
    )
RETURN
    COUNTROWS ( EXCEPT ( VALUES ( SampleData[alumni_id] ), _pastDonors ) )


Repeat Donors = 
VAR _pastDonors =
    CALCULATETABLE (
        VALUES ( SampleData[alumni_id] ),
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] < MIN ( 'Calendar'[Date] ) )
    )
RETURN
    COUNTROWS ( INTERSECT ( VALUES ( SampleData[alumni_id] ), _pastDonors ) )

Please see the attached pbix.

[bhanu_gautam]

@powerbiexpert22 

Ensure you have a Date table in your model with a continuous range of dates, including the year 2024.

New Donors Measure

New Donors =
VAR CurrentMonth = MAX('Date'[Month])
VAR CurrentYear = MAX('Date'[Year])
VAR CurrentDate = MAX('Date'[Date])
VAR PreviousDonations =
CALCULATETABLE(
VALUES('Donations'[alumni_id]),
'Donations'[donation_date_key] < CurrentDate,
'Donations'[donation_date_key] >= DATE(CurrentYear - 1, 12, 1)
)
RETURN
COUNTROWS(
FILTER(
VALUES('Donations'[alumni_id]),
NOT('Donations'[alumni_id] IN PreviousDonations) &&
CALCULATE(MIN('Donations'[donation_date_key])) >= DATE(CurrentYear, CurrentMonth, 1) &&
CALCULATE(MIN('Donations'[donation_date_key])) < EOMONTH(CurrentDate, 0)
)
)

Repeat Donors Measure

DAX
Repeat Donors =
VAR CurrentMonth = MAX('Date'[Month])
VAR CurrentYear = MAX('Date'[Year])
VAR CurrentDate = MAX('Date'[Date])
VAR PreviousDonations =
CALCULATETABLE(
VALUES('Donations'[alumni_id]),
'Donations'[donation_date_key] < CurrentDate,
'Donations'[donation_date_key] >= DATE(CurrentYear - 1, 12, 1)
)
RETURN
COUNTROWS(
FILTER(
VALUES('Donations'[alumni_id]),
'Donations'[alumni_id] IN PreviousDonations &&
CALCULATE(MIN('Donations'[donation_date_key])) >= DATE(CurrentYear, CurrentMonth, 1) &&
CALCULATE(MIN('Donations'[donation_date_key])) < EOMONTH(CurrentDate, 0)
)
)

Steps to Create the Line Chart
Add a slicer to your report to allow users to select the year.
Add a line chart visual to your report.
Configure the Line Chart:
Set the X-axis to the 'Month' field from your Date table.
Add the "New Donors" and "Repeat Donors" measures to the Values field.