Dynamic CAGr with different accounts and different beginning dates based on filters/slicers

[Anonymous]

1. I need to calculate CAGR by our Fiscal Year (which starts in November) for balances for several different accounts based on the open (beginning) date of the account.
2. For example account A opened November 25, 2015, Account B opened June 2, 2009, Account C opened February 15, 2019.
3.  The Fiscal year begins in November of each year
4. Not sure if this is relevant, but the balances are already cumulative; i.e. the balance for the most current month is the sum total of all previous months/years.
5. If an account doesn't have a full year's history, I'd like it to be able to pro-rate CAGr based on the month we're currently in. 
6. I have a table already set up for our fiscal years, but it's in a different table than my accounts and balances and open dates. 
7. Ultimately, I need to be able to select an account based on a slicer or some other filter (I already have set up); it should then calculate the CAGr in a card or table visual based on the selected account.  Behind the scenes, it should also know the beginning dates of each account (which is used to calculate CAGr).     
8. Unfortunately, I can't send a sample file due to the confidentiality of the data, but hoping this is fairly intutive the way I've explained it here. 

[v-juanli-msft]

Hi @Anonymous 

From your information, I have a table structure as below

account	open dates	blance	blance2
A	11/25/2015	1	1
A	11/29/2015	1	2
A	12/10/2015	1	3
B	7/2/2009	1	1
B	7/3/2009	1	2
C	2/15/2009	1	1
C	2/16/2009	1	2
C	2/17/2009	1	3

I think your [balance] may look like my [balance 2] above.

But i wonder is your [balance] a measure or a column in original data source?

If it is a measure, it calcualtes based on another column to get a cumulative value, right?

Last but not least, what is "CAGR"?

How to calculate it(rule?)

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.