generate data list of donors that donot exist during a time range

[Kumail]

I have some data showing donor contributions over time. (Means Donor number, contribution amount and date). What I have been trying to achieve is at any date, list of contributors who have not made any contribution during the past 3 months and less than 3 contributions in overall 6 months. I have been trying to do this with dax however with no success at all. Any help would be very appreciated.

[Anonymous]

Hi @Kumail,

You can refer to below formula if them suitable for your requirement.:

Table formula:

last 3 month not donate.

Last 3 Month not donate = 
var currDate=LASTDATE(Sheet2[Date])
var donorList=DISTINCT(SelectColumns(FILTER(ALL(Sheet2),Sheet2[DATE]<=currDate),"Donor Number",[Donor Number]))
var donorList3Month=DISTINCT(SelectColumns(FILTER(ALL(Sheet2),Sheet2[DATE]>=DATE(Year(currDate),MONTH(currDate)-3,DAY(currDate))),"Donor Number",[Donor Number]))
return
ADDCOLUMNS(EXCEPT(donorList,donorList3Month),"Note","Last 3 Month not donor")

Temp table with last 6 month records.

temp 2 = 
var currDate=LASTDATE(Sheet2[Date])
var temp= FILTER(ALL(Sheet2),[DATE]>=DATE(Year(currDate),MONTH(currDate)-6,DAY(currDate)))
Return
temp

Table formula:  last 6 month donate amount less than 3

Last 6 month donor less 3 = SELECTCOLUMNS(FILTER(SUMMARIZE('temp 2',[Donor Number],"Total",SUM('temp 2'[Donor Amount])),[Total]<3),"Donor Number",[Donor Number],"Note","Last 6 month donor less 3")

Regards,

Xiaoxin Sheng

[Kumail]

Hi @Anonymous

Thanks for the tables. When applied, it is getting list of lost contributors, however, the contributors that are to be coming during the upcoming years also appear as lost contributors. As you can see in the image.

Ideally it should show lost contributors that -has contributed less then 3 times during preceeding 6 months and - no contribution for last 3 months. 

These filters would be for any month or date selected.

[Anonymous]

Hi @Kumail,

You can try to use below formula:

Measures:

Choosen = IF(HASONEVALUE(Sheet2[Date]),VALUES(Sheet2[Date]),BLANK()) // get selected data

Last 3 Month not donor = 
var currDate=[Choosen]
var donorList=DISTINCT(SelectColumns(FILTER(ALL(Sheet2),Sheet2[DATE]<=currDate),"Donor Number",[Donor Number]))
var donorList3Month=DISTINCT(SelectColumns(FILTER(ALL(Sheet2),Sheet2[DATE]>=DATE(Year(currDate),MONTH(currDate)-3,DAY(currDate))),"Donor Number",[Donor Number]))
return
CONCATENATEX(ADDCOLUMNS(EXCEPT(donorList,donorList3Month),"Note","Last 3 Month not donor"),[Donor Number],",")

Last 6 month donor less 3 = 
var currDate=[Choosen]
var temp= FILTER(ALL(Sheet2),[DATE]>=DATE(Year(currDate),MONTH(currDate)-6,DAY(currDate)))
Return
CONCATENATEX(SELECTCOLUMNS(FILTER(SUMMARIZE(temp,[Donor Number],"Total",SUM([Donor Amount])),[Total]<3),"Donor Number",[Donor Number],"Note","Last 6 month donor less 3"),[Donor Number],",")

Regards,

Xiaoxin Sheng

[Kumail]

Thanks @Anonymous.

While trying the solution, system gives wrong data type or value too small or too big error. 

Tried using this as measure, however, no success. 

[Anonymous · Accepted solution]

Hi @Kumail,

I upload the sample file below, perhaps you can refer to it.

Regards,

Xiaoxin Sheng