Second Gift Date

[UK_User123456]

Hi All,

I work for a non profit and starting to use power bi and wanted to show the number of donors that have given in FY 2018/19 for the first time and then given again for the second time?

as in the example below 

Donor 1234 first gave on 12/04/2018 and then in the gift table gave again on 15/05/2018, this would also apply to donor ID 1243. Donor ID 2345 and 3456 would be disregarded as they dont have any second gift.

Donor Table				Gift Table
Donor ID	First Gift Date			Donor ID	Gift Date	Gift Date 2	Gift Date 3
1234	12/04/2018				1234	15/05/2018	13/01/2019
2345	18/06/2018				2345
3456	01/02/2018				3456
1243	21/08/2018				1243	13/12/2018		16/01/03/2019

Im not too worried about gift 2 and 3, I just dont know how to insert a measure that will check the first gift and then check if the donor who gave the first has then given again. 

After reading through the community I can see that others have used variables and declared them and used the min/max, but im not sure if I am on the right lines?

Please let me know if this makes sense?

[UK_User123456 · Accepted solution]

thanks @MitchM , however, it only return the very last gift date, I need it to be able to show the first gift as well, so as in the example below:

Gift Table

Donor ID         First donor date                Second donor date             Third donor date

1234                12/04/2018                       15/05/2018            

2345                 06/04/2018

1111                07/06/2018                        12/06/2018                         21/08/2018

The end result would be:

Donor ID         First donor date                Second donor date             

1234                12/04/2018                       15/05/2018            

2345                 06/04/2018

1111                07/06/2018                        12/06/2018                         

So not matter if the donor had donated 5 times, I would only want to see the first and second donor date if that makes sense?

Apologies if im confusing you, its almost there and what you have provided does kind of work, but I wouldnt know where to start to fix it.

[MitchM · Accepted solution]

It is no suprise that the filtering does not work. The ranking needs another value (In this case FY) to rank against. To do this you can create a new calc. column called FY:

FY = 
VAR FirstMonthofFY = 9
VAR DateYear = YEAR( GiftData2[Reformatted Date] )
VAR DateMonth = MONTH( GiftData2[Reformatted Date] )
RETURN
    IF(
        DateMonth < FirstMonthofFY,
        (DateYear - 1) & "/" & DateYear,
        DateYear & "/" & (DateYear + 1)
    )

I am not sure when your FY starts, so just change the first variable (FirstMonthofFY) in the formula above to adjust the FY range. Once this is done a slight tweak to the Rank formula (below) should finish it off. The two dontation measures do not need to be adjusted.

Gift_Date_Rank = 
    RANKX(
        FILTER(
            GiftData2,
            GiftData2[Donor ID] = EARLIER( GiftData2[Donor ID] ) &&
            GiftData2[FY] = EARLIER( GiftData2[FY] )
        ),
        GiftData2[Reformatted Date],
        ,
        ASC,
        Dense
    )

[vanessafvg]

@UK_User123456  do you want to just work out how many times they have given or do you want the exact dates.

I pivoted your data and then did a count on where the date was not null, this might work for you.

[MitchM]

I took a stab at this. I operated under the assumption that you may want to define what your FY date range is, so you can do that in the measure. Hopefully this works for you.

Given In FY = 
VAR FiscalYearStart = DATE(2018,03,01) //Date Function is Year,Month,Day
VAR FiscalYearEnd = DATE(2019,02,28)
RETURN 
    IF(
        CALCULATE(
        MAX( 'Donor Table'[First Gift Date] ),
        ALLEXCEPT( 'Donor Table', 'Donor Table'[Donor ID] ),
        'Donor Table'[First Gift Date] >= FiscalYearStart,
        'Donor Table'[First Gift Date] <= FiscalYearEnd
        ) = BLANK(),
    "Has Not Given In FY",
    "Has Given in FY"
    )

Additional Giving In FY = 
VAR FiscalYearStart = DATE(2018,03,01) //Date Function is Year,Month,Day
VAR FiscalYearEnd = DATE(2019,02,28)
VAR DonorId = SELECTEDVALUE( 'Donor Table'[Donor ID] )
RETURN 
           
        IF(
            CALCULATE(
            MAX( 'Gift Table'[Gift Date] ),            
            'Gift Table'[Gift Date] >= FiscalYearStart,
            'Gift Table'[Gift Date] <= FiscalYearEnd,
            FILTER( 'Gift Table', 'Gift Table'[Donor ID] = DonorId )
            ) = BLANK(),
            "No Additional Giving",
            "Additional Giving"
        )
    

[UK_User123456]

@Mitchem, many thanks for your response, this is something that looks like I want, but what happens if there was no donor table and I wanted to just use the gift table only?

[UK_User123456]

Also, I dont want to specify the date as I will have a FY already as a filter.

[UK_User123456]

Also, if I wanted to return the date rather than the text of Has or Hasnt given, is this possible as well?

TIA

[MitchM]

So if you only have the single gift table, and you do not need to worry about defining your date range, the measure become much simpler. Here is one to check for giving:

First Donor Gift Date = 
    CALCULATE(
        MAX( 'Gift Table'[Gift Date] )  
    )

From your initial question I am guessing you just need to know if they ever make a second donation, so here is the modified additional gift measure:

  IF(
        CALCULATE(
        MAX( 'Gift Table'[Gift Date 2] )                      
        ) = BLANK(),
        "No Additional Giving",
        "Additional Giving"
    )

Does this work?

[UK_User123456]

What happens if I wanted to return the actual date of the first gift and the second gift, so that I can then do a datediff to find out the difference between the two dates?

[MitchM]

That is doble, you could use:

Additional Giving In FY = 
    CALCULATE(
        MAX( 'Gift Table'[Gift Date 2] )
    )

[UK_User123456 · Accepted solution]

thanks @MitchM , however, it only return the very last gift date, I need it to be able to show the first gift as well, so as in the example below:

Gift Table

Donor ID         First donor date                Second donor date             Third donor date

1234                12/04/2018                       15/05/2018            

2345                 06/04/2018

1111                07/06/2018                        12/06/2018                         21/08/2018

The end result would be:

Donor ID         First donor date                Second donor date             

1234                12/04/2018                       15/05/2018            

2345                 06/04/2018

1111                07/06/2018                        12/06/2018                         

So not matter if the donor had donated 5 times, I would only want to see the first and second donor date if that makes sense?

Apologies if im confusing you, its almost there and what you have provided does kind of work, but I wouldnt know where to start to fix it.

[MitchM]

Quick question, because I think I must be missing something. Will your gift table be 2 columns with ID's and Dates or will it be as you showed, with a seperate column for each gift (1,2,3,etc.)? If it is laid out as you show it is there a reason you can not just place the first and second gift columns in a table? If you have a sample pbix file you can share or an excel file with a sample data set that you can share that would be much easier than me guessing 🙂

[UK_User123456]

I have put below the sample of exactly how the data will be laid out and highlighted the outcome of what would be displayed in terms of gift 1 and gift 2.

Donor ID	Gift ID	Gift Date	Gift Amount	Gift Code	Gift Type
1234	111111	12/05/2018	2	Voucher	Cash
1234	111112	13/05/2018	15	Voucher	Cash
1234	111113	14/05/2018	45	Voucher	Cash
1234	111114	15/05/2018	12	Voucher	Cash
2344	111115	16/05/2018	100	Gift In Kind	Cash
2345	111116	17/05/2018	90	Online	Cash
4343	111117	18/05/2018	3.5	Gift In Kind	Cash
1111	111118	19/05/2018	50	Online	Cash
1111	111119	20/05/2018	9	Online	Cash
2222	111120	21/05/2018	10	Gift In Kind	Cash
3333	111121	22/05/2018	32	Gift In Kind	Cash
3333	111122	23/05/2018	12	Gift In Kind	Cash
3333	111123	24/05/2018	34	Online	Cash
6666	111124	25/05/2018	54	Online	Cash
7777	111125	26/05/2018	76	Gift In Kind	Cash
8888	111126	27/05/2018	78	Gift In Kind	Cash
8888	111127	28/05/2018	11	Gift In Kind	Cash

Thank you.

---

[UK_User123456]

any help would be greatly appreciated.

[MitchM]

Ok, I think I got it now. First off, I did go into the query editor and reformat the date to MM/DD/YYYY. This requires creating a calculated column that will rank each date for each donor (Gift_Date_Rank in pic). The measure the calculates the 1st rank (for First Gift) and 2nd rank (for Second Gift).

Hope this makes sense.

CALCULATED COLUMN
Gift_Date_Rank = 
    RANKX(
        FILTER(
            GiftData,
            GiftData[Donor ID] = EARLIER( GiftData[Donor ID] )
        ),
        GiftData[Reformatted Date],
        ,
        ASC,
        Dense
    )

First_Donation = 
    CALCULATE(
        MIN( GiftData[Reformatted Date] ),
        GiftData[Gift_Date_Rank] = 1
    )


Second_Donation = 
    CALCULATE(
        MIN( GiftData[Reformatted Date] ),
        GiftData[Gift_Date_Rank] = 2
    )

[UK_User123456]

I will let you know if it has worked next week, but from your screenshot that you have provided, this looks absolutely spot on. Looks very complicated. Are you able to walk me through how the rank works as this is not something I have come across? and so that I can make notes within the calc to be able to remember what it is doing.

On a side note to this, would it be the same calculation or working if I wanted to get the ranking for the last two gift dates?

[MitchM]

Ranking explained below. If you wanted to get the last 2 dates you would need to create a second calculated column and change from ASC to DESC. 

CALCULATED COLUMN
Gift_Date_Rank = 
    RANKX(
 // You need to filter down the dataset to the subset you want ranked. In this case it is all the dates where the Donor ID's match
 // This is done by saying "Hey Power BI, filter GiftData to this row and any row after it that matches (EARLIER)
        FILTER(
            GiftData,
            GiftData[Donor ID] = EARLIER( GiftData[Donor ID] )
        ),
 // Next we need to define the value to be ranked, in this case it is the date column
        GiftData[Reformatted Date],
        ,
 // You want the first gift, so we rank ASC so that the first is 1, second is 2, etc.
        ASC,
 // We now dense rank to account for potential duplicate donations on the same day. The will both be 1, but the second day would be 2
        Dense
    )

[UK_User123456]

That is an awesome way of doing things and I would never have thought of it. You are awesome so many thanks!

[MitchM]

No problem! If this works please mark the solution as accepted to close it off. Have a great weekend!

[UK_User123456]

@MitchM- Your calculation works, however one slight problem( table below:) is that, as some donors may have given gifts in 1998 so it is taking this as the first gift, so when I apply a filter for FY 18/19, it is not showing the first gift and second gift correctly also to make things more complicated, the donor could have given 2 gifts on the same day but at different times.

Is there anyway around this?

Gift ID	Gift Date	Gift Amount	Gift Code	Gift Type	Post Date	Donor ID
1	06/04/1998	£10.00	Standing order	Cash	12/04/1998	1010
2	08/05/1999	£10.00	Standing order	Cash	09/05/1999	1010
3	06/06/2000	£10.00	Standing order	Cash	12/06/2000	1010
4	06/07/2004	£10.00	Standing order	Cash	14/07/2004	1010
5	06/08/2005	£10.00	Standing order	Cash	08/08/2005	1010
6	06/09/2018	£10.00	Standing order	Cash	09/09/2018	1010
7	08/10/2018	£10.00	Standing order	Cash	21/10/2018	1010
8	06/05/2019	£10.00	Standing order	Cash	12/05/2018	1010
9	22/08/2009	£100.00	Direct donation	Cash	01/09/2009	202
10	12/06/2018	£235.30	InMem	Cash	13/06/2018	202
11	06/05/2019	£10.00	Standing order	Cash	22/05/2019	300
12	06/05/2019	£100.00	Direct donation	Cash	22/05/2019	300

TIA

[MitchM · Accepted solution]

It is no suprise that the filtering does not work. The ranking needs another value (In this case FY) to rank against. To do this you can create a new calc. column called FY:

FY = 
VAR FirstMonthofFY = 9
VAR DateYear = YEAR( GiftData2[Reformatted Date] )
VAR DateMonth = MONTH( GiftData2[Reformatted Date] )
RETURN
    IF(
        DateMonth < FirstMonthofFY,
        (DateYear - 1) & "/" & DateYear,
        DateYear & "/" & (DateYear + 1)
    )

I am not sure when your FY starts, so just change the first variable (FirstMonthofFY) in the formula above to adjust the FY range. Once this is done a slight tweak to the Rank formula (below) should finish it off. The two dontation measures do not need to be adjusted.

Gift_Date_Rank = 
    RANKX(
        FILTER(
            GiftData2,
            GiftData2[Donor ID] = EARLIER( GiftData2[Donor ID] ) &&
            GiftData2[FY] = EARLIER( GiftData2[FY] )
        ),
        GiftData2[Reformatted Date],
        ,
        ASC,
        Dense
    )

[UK_User123456]

@MitchM- many thanks for helping with this, it has worked wonders, however, if I wanted to show the campaign(s) against these donations, would I need to follow the same path? I would like to show the campaign(s) for both the first donation and second donation?

Thanks