Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Lapsed Donors Calculated Column

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

2 REPLIES 2
Anonymous
Not applicable

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?

 

 

Hammiec67_0-1682667468257.png

Thanks


Chris

 

Ghhousuddin
Resolver I
Resolver I

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors