Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Hi,
You may download my PBI file from here.
Hope this helps.
@Ashish_Mathur Thanks! Would there be a way to do this if there were more than 2 years? For example, my data runs 2015 - 2019. I would like to see: of accounts that existed in 2015, which accounts had revenue in 2016, and 2017, 2018, 2019.
I also need to eventually get to a view of a 2016, 2017, and 2018 cohorts. So, of accounts that had revenue in 2016, which had revenue in 2017, 2018, and 2019, and so on for each cohort. Is this possible in power bi?
Hi,
Share some data and show the expected result with an explanation.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |