Transfering column data into multiple columns with DAX

[npatten]

 Transfer column entries into multiple columns in DAX

Hi- I work for a hospital and I need some DAX help. I am looking to take a column of donation dates and split it into multiple columns, but preserving the same row/column structure. I need to be able to calculate the sum of annual donation totals, but based on the presence/absence of donations in other years. For example, someone who donated in 2015, but not 2016 or 2017. We use these kinds of binary filtering to figure out donors who are lapsed, recaptured, new, etc. I have tried making a number of CALCULATE(SUMX columns using DATESBETWEEN but I can only get one column. Successive attempts return ‘circular dependency’ alerts. I’ve also tried GROUPBY, SUMMARIZE, and simple SUMX(FILTER columns without success. I need columns because calculated measures cannot be used for advanced filtering. Here is how my data is structured now: 

Name	Date	Amount	RevenueCenter
John	2015	100	Cardiology
John	2016	100	Oncology
John	2017	100	Neurology
Joe	2015	100	Cardiology
Joe	2016	100	Oncology
Joe	2017	100	Neurology
Mary	2015	100	Cardiology
Mary	2016	100	Oncology
Mary	2017	100	Neurology
Deb	2015	100	Cardiology
Deb	2016	100	Oncology
Deb	2017	100	Neurology

Here is what I would like it to look like:

Name	Date	Amount	2015	2016	2017	RevenueCenter
John	2015	100	100			Cardiology
John	2016	100		100		Oncology
John	2017	100			100	Neurology
Joe	2015	100	100			Cardiology
Joe	2016	100		100		Oncology
Joe	2017	100			100	Neurology
Mary	2015	100	100			Cardiology
Mary	2016	100		100		Oncology
Mary	2017	100			100	Neurology
Deb	2015	100	100			Cardiology
Deb	2016	100		100		Oncology
Deb	2017	100			100	Neurology

Thank you for much!

[Phil_Seamark]

Hi @npatten

Yes it's possible to manipulate the data as you suggest in both DAX and the Query Editor, but I suggest you keen the data as is and use another method to derive the Lapsed, Recaptured flags for reporting.

If you'd like help with that then let me know, thought your test dataset might need to mix it up a little to show difference results working correctly.

[npatten]

@Phil_SeamarkThank you for your offer of help. I would certainly like to discuss your ideas. What did you have in mind? 

Thanks!

[Phil_Seamark]

I guess just a bit more sample data in the orignal format but mix up the numbers dates and values a bit so we can generate some DAX to return the results you need.  

[npatten]

@Phil_SeamarkWow! Thank you for your offer to help. I'll DM you some data and we can go from there. Thanks!