Lapsed Donors Calculated Column

[Anonymous]

Hi,

This is my first post but I have been using the forum for reaserch for a few years now.

My dataset is made of Donations data and I am trying to create a column which has either "Lapsed" or "Not Lapsed" against each line item that relates to the donor. I have a measure that counts the Lapsed Donors but I'm trying to figure out how I get a list of the donors who are included in that Measure.

Thanks

Chris

[Anonymous]

Hi,

Thanks for coming back to me so quickly and explaining it clearly.

I have created the column but unfortunately it isn't quite working right as there is no lapsed donors. Below is a screen shot with the data set including the latest Payment date, so I can only assume the MAX Payment date element isn't pulling the latest date, I had this issue before when trying something similar. 

*Lapsed Donors = IF (DATEDIFF(MAX('Charity Income Summary'[Payment date]),TODAY(),MONTH)>12,"Lapsed","Not Lapsed")

The Payment date is linked to a date table if that might affect it?

Thanks

Chris

[Ghhousuddin]

To create a column in your Donations dataset that indicates whether a donor has lapsed or not, you can follow these steps:

1. Determine your definition of a lapsed donor. For example, you may define a lapsed donor as someone who has not donated in the past 12 months.

2. Create a calculated column in your Donations table using the following DAX formula:

```
Lapsed = IF(DATEDIFF(MAX(Donations[Date]), TODAY(), MONTH) > 12, "Lapsed", "Not Lapsed")
```

This formula uses the DATEDIFF function to calculate the number of months between the last donation date for each donor and today's date. If the result is greater than 12, the donor is considered lapsed, and the "Lapsed" value is returned in the Lapsed column. Otherwise, the "Not Lapsed" value is returned.

3. Once you have created the Lapsed column, you can use it to filter your data and view a list of lapsed donors. For example, you can add a table or matrix visualization to your report and include the Lapsed column and Donor column in the Rows area. Then, you can use the filter pane to filter the Lapsed column to show only the "Lapsed" values. This will display a list of donors who have not donated in the past 12 months and are therefore considered lapsed.

4. You can also create a measure that counts the number of lapsed donors using the following DAX formula:

```
Lapsed Donors = COUNTROWS(FILTER(Donations, Donations[Lapsed] = "Lapsed"))
```

This formula uses the FILTER function to create a table that includes only the rows where the Lapsed column has a value of "Lapsed". Then, the COUNTROWS function is used to count the number of rows in this table, which represents the number of lapsed donors. You can add this measure to a card or other visualization to display the total number of lapsed donors in your dataset.