Forum Discussion
Second Gift Date
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?
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.
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 )Another question and hopefully the last - once I have the campaign id's and how when the donors gave their 1st and 2nd gift, how would I go about doing a count of how many times the campaign id has appeared, so for example
In 2019 for Jan - there were 200 donors that donated a gift on campaign id "2", out of the 200 donors that gave on campaign id "2", 100 donors gave their second gift on campaign id "3", and 50 donors gave on campaign "4" and 50 donors never gave a second gift..
So its trying to identify out of all the donors that gave their first gift and to which campaign gave again and if it was to a different campaign code or not.
Hope that makes sense
TIA
24 Replies
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" )Anonymous, 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?
Also, I dont want to specify the date as I will have a FY already as a filter.
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.
