Hi,
I've come across an interesting dilemma: I'm trying to calculate an attrition measure based on a yearly cohort. For example, I'd like to see: of customers that had revenue in 2015, how much revenue did those same customers have in 2016.
I have two tables: "Accounts" and "Projects".
Each project has an associated account. So, a certain account could have 3 or 4 projects in a year. However, revenue is only stored by project in the "projects" table. See below:
| Project | Account | Revenue | Date |
| Project A | Client A | 5,000 | 5/1/2015 |
| Project B | Client A | 10,000 | 10/1/2015 |
| Project C | Client A | 7,000 | 12/31/2015 |
| Project D | Client B | 8,000 | 7/1/2015 |
| Project E | Client B | 10,000 | 7/1/2016 |
| Project F | Client C | 4,000 | 3/1/2016 |
| Project G | Client D | 6,000 | 3/31/2015 |
| Project H | Client D | 5,000 | 9/30/2015 |
| Project I | Client D | 2,000 | 3/31/2016 |
I need to first get to revenue by year by account, which I can do by using the "matrix" visualization:
| Account | 2015 Revenue | 2016 Revenue |
| Client A | 126,772 | 0 |
| Client B | 15,000 | 10,000 |
| Client C | 0 | 4,000 |
| Client D | 11,000 | 2,000 |
However, from those results, I then need to perform calculations. I need to be able to calculate how much revenue did a client have in 2016 if, and only if, they had revenue in 2015. See below:
| Account | 2016 revenue (2015 cohort) |
| Client A | 0 |
| Client B | 10,000 |
| Client C | 0 |
| Client D | 2,000 |
My question is, is there a way to do this through DAX formulas? I'd like to create a new column in my "accounts" table that will sum the revenue by year by account from the "projects" table (again, revenue is not stored in the accounts table. It is only stored in the projects table).
I know that a SUMX function will allow me to get a total row sum based on filtering criteria from a different table, but I need to basically have it do this for each individual row rather than the entire column in aggregate.
Is this possible?
