Second Gift Date

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 :)

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.

Thank you.

any help would be greatly appreciated.

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
    )

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?

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
    )

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

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

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?

TIA

It seems to work with no filters applied, but as soon as I apply a financial year, it then doesnt work.

holy moly, this would be easier in excel, but im appreciative of all your help

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

No worries! Yes, if you wanted to add an extra dimension to the ranking you would need to add it to the Rank calculated column in a similar way I added FY. Just rember that for every subcategory you add to the rank equation is further slicing down your data. You could always add a second rank column (and additional measures) if you want to rank by another subcategory.