Create a calculated column that selects the most recent text from a different table

[Anonymous]

Hello! I am going in circles. I have a table of donors and a table of gifts in a one to many relationship. In the Donor table, I would like to create a calculated column that looks at all the gifts for each donor and then writes the most recent Solicit Code (based on Gift Date) to the calculated column. My main problem is that I don't have anything to aggregrate so most of the examples don't apply.

Donor ID	Gift Date	Solicit Code
1	1/1/2016	Banquet
1	1/1/2017	Fun Run
1	1/1/2018	Appeal
1	1/1/2019	Online

In this case, the most recent gift is 1/1/2019 so the Most Recent Solicit Code would be "Online" and that's what I would like written into the calculated column field for Donor 1.

Once I figure out how to do this, it is going to be very helpful in our data analysis, so thank you in advance for your assistance!

[d_gosbell]

If you want to do this as a calculated column on the example table you provided above you could use the following code:

Latest Solicit Code Column =

VAR _lastDate = CALCULATE(MAX(Table1[Gift Date]), ALLEXCEPT(Table1,Table1[Donor ID]))

VAR _result = CALCULATE(MAX(table1[Solicit Code]), FILTER(Table1, table1[Gift Date] = _lastDate ))

RETURN _result

[Anonymous]

Ideally, I need the calculated column on the Donor table, not the Gift table. How do I move a string of text from a column in one table to a column in another?

[christianfcbmx]

Hi @Anonymous , if those tables are correctly related, you can create a matrix table on the canvas and use the columns you want.

or if you prefer, send me the pbix to give it a try.

[d_gosbell · Accepted solution]

You can use similar basic logic, if you can guarentee that you don't have multple gifts from the one Donor on a given day you could use the relatedtable and lookup functions

Latest Solicit Code = 
VAR _lastDate = MAXX(RELATEDTABLE(Gift), Gift[Gift Date] ) 
VAR _result = LOOKUPVALUE(Gift[Solicit Code], Gift[Donor ID], Donor[Donor ID], Gift[Gift Date], _lastDate)
RETURN _result

If you can't guarentee that there won't be more than one gift from an given donor on a given day you could use the following, but note that it will just grab the "max" Solicit Code (so probably the last one when sorted alphabetically)

Latest Solicit Code 2 = 
VAR _lastDate = MAXX(RELATEDTABLE(Gift), Gift[Gift Date] ) 
VAR _currentDonor = Donor[Donor ID]
VAR _result = CALCULATE(MAXX(FILTER(Gift, Gift[Donor ID] = _currentDonor && Gift[Gift Date] = _lastDate) , Gift[Solicit Code]))
RETURN _result

[Anonymous]

Thank you! I used the second formula because we have split gifts that have the same gift date.  Generally, they have the same solicit code but if they don't it's fine to use whatever the formula decides is MAX.