Monthly percentage rolling return

[kar2022]

Hi,

I have a Merge Table with multiple players and one Leader.  I want to calculate the total monthly Gain (or loss) on a rolling basis. and compare side-by-side for each player with the leader.   If possible, I would like to forcast Player's and Leader's performance based on the "Forecast" tool.    I can also separate as PlayerA and Leader.

Date	Player	Gains		Date	Player	Gains
12/1/20	PlayerA	4.21%		12/1/20	Leader	3.71%
1/1/21	PlayerA	0.65%		1/1/21	Leader	-1.11%
2/1/21	PlayerA	1.87%		2/1/21	Leader	2.61%
3/1/21	PlayerA	0.48%		3/1/21	Leader	4.24%
4/1/21	PlayerA	3.18%		4/1/21	Leader	5.24%
12/1/20	PlayerB	2.10%		12/1/20	Leader	3.71%
1/1/22	PlayerB	0.32%		1/1/21	Leader	-1.11%
2/1/21	PlayerB	0.93%		2/1/21	Leader	2.61%
3/1/21	PlayerB	0.24%		3/1/21	Leader	4.24%
4/1/21	PlayerB	1.59%		4/1/21	Leader	5.24%

Player and Leader starting with $10,000
Date	PlayerA	$10,000		Leader	$10,000
12/01/20	4.21%	$10,421		3.71%	$10,371
01/01/21	0.65%	$10,488		-1.11%	$10,256
02/01/21	1.87%	$10,684		2.61%	$10,524
03/01/21	0.48%	$10,735		4.24%	$10,970
Total Return	7.35%			9.70%

[Anonymous · Accepted solution]

Hi @kar2022, 

According to your description, you want to forcast Player's and Leader's performance based on the "Forecast" tool. Here are my steps you can follow as a solution.

(1)This is my test data. 

Table “Leader” 

Table “Player” 

 The relationship between the tables is shown in the following diagram. 

 (2)We can create  measures:  

10000-Player = IF( SUMX( FILTER( ALL('Player') , 'Player'[Date] <= SELECTEDVALUE('Leader'[Date]) && 'Player'[Player]= SELECTEDVALUE('Player'[Player])  ) , [Gains]) * 10000 <> BLANK() ,  SUMX( FILTER( ALL('Player') , 'Player'[Date] <= SELECTEDVALUE('Leader'[Date]) && 'Player'[Player]= SELECTEDVALUE('Player'[Player]) ) , [Gains]) * 10000+10000)   

10000-Leader = SUMX( FILTER( ALL('Leader') , 'Leader'[Date]<=            SELECTEDVALUE('Leader'[Date])  ) , [Gains]) *10000+10000 

 

(3)Then the result is as follows. 

If this method does not meet your needs, you can provide us with detailed input and output examples in tabular form so that we can better solve the problem for you. 

Best Regards, 

Neeko Tang 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[kar2022]

Hi Neeko,

Thank you very much.  It worked.