DAX Calculation

[Anonymous]

Hi,

Issue:

The date range has to be flexible. (Slider) 

Parameter 1 = Amount Invested

Parameter 2 = Amount withdrawn at the end of each year

Say I invest $1,000,000 (Parameter 1) in a fund in 1999.

At the end of each year since 1999, I should withdraw $50,000 (parameter 2) from the total net amount (Growth) at that point in time. With this rule I need to project my investments for the following 20 years (2019) (Date slider).

I have data of the funds "Daily performance" through 1999, hence I can get the net amount (Growth) at any point in time by a cummulative calculation (Below). 

Cummulative performance:

Growth = 
CALCULATE(
    PRODUCTX(Table, 1 + (Table[Performance]/100)),
    FILTER(ALLSELECTED(Table[AsOfDate]), ISONORAFTER(Table[AsOfDate], MAX(Table[AsOfDate]), DESC)
    )) * Parameter 1

Output for Cummulative performance:

I am trying to script the growth calculation with a "Withdrawal condition". Any help would be appreciated.

Sample Data:

AsOfDate	DataPointDescription	Performance	InstrumentDescription
3/1/2019 0:00	Russell 1000 Index TR  - Net Dividends 1 Day	0.6795	Russell 1000
3/1/2019 0:00	Russell 3000 Index TR  - Net Dividends 1 Day	0.6961	Russell 3000
2/28/2019 0:00	Russell 1000 Index TR  - Net Dividends 1 Day	-0.2667	Russell 1000
2/28/2019 0:00	Russell 3000 Index TR  - Net Dividends 1 Day	-0.2713	Russell 3000
2/27/2019 0:00	Russell 1000 Index TR  - Net Dividends 1 Day	0.0257	Russell 1000
2/27/2019 0:00	Russell 3000 Index TR  - Net Dividends 1 Day	0.041	Russell 3000
2/26/2019 0:00	Russell 1000 Index TR  - Net Dividends 1 Day	-0.1186	Russell 1000
2/26/2019 0:00	Russell 3000 Index TR  - Net Dividends 1 Day	-0.1622	Russell 3000
2/25/2019 0:00	Russell 1000 Index TR  - Net Dividends 1 Day	0.1435	Russell 1000
2/25/2019 0:00	Russell 3000 Index TR  - Net Dividends 1 Day	0.1273	Russell 3000
2/22/2019 0:00	Russell 1000 Index TR  - Net Dividends 1 Day	0.6727	Russell 1000
2/22/2019 0:00	Russell 3000 Index TR  - Net Dividends 1 Day	0.6911	Russell 3000
2/21/2019 0:00	Russell 1000 Index TR  - Net Dividends 1 Day	-0.3465	Russell 1000
2/21/2019 0:00	Russell 3000 Index TR  - Net Dividends 1 Day	-0.349	Russell 3000
2/20/2019 0:00	Russell 1000 Index TR  - Net Dividends 1 Day	0.1782	Russell 1000
2/20/2019 0:00	Russell 3000 Index TR  - Net Dividends 1 Day	0.1987	Russell 3000
2/19/2019 0:00	Russell 1000 Index TR  - Net Dividends 1 Day	0.1555	Russell 1000
2/19/2019 0:00	Russell 3000 Index TR  - Net Dividends 1 Day	0.1686	Russell 3000
2/15/2019 0:00	Russell 1000 Index TR  - Net Dividends 1 Day	1.0765	Russell 1000
2/15/2019 0:00	Russell 3000 Index TR  - Net Dividends 1 Day	1.1124	Russell 3000
2/14/2019 0:00	Russell 1000 Index TR  - Net Dividends 1 Day	-0.2117	Russell 1000
2/14/2019 0:00	Russell 3000 Index TR  - Net Dividends 1 Day	-0.1843	Russell 3000
2/13/2019 0:00	Russell 1000 Index TR  - Net Dividends 1 Day	0.3025	Russell 1000
2/13/2019 0:00	Russell 3000 Index TR  - Net Dividends 1 Day	0.3031	Russell 3000
2/12/2019 0:00	Russell 1000 Index TR  - Net Dividends 1 Day	1.2857	Russell 1000
2/12/2019 0:00	Russell 3000 Index TR  - Net Dividends 1 Day	1.2846	Russell 3000
2/11/2019 0:00	Russell 1000 Index TR  - Net Dividends 1 Day	0.1091	Russell 1000
2/11/2019 0:00	Russell 3000 Index TR  - Net Dividends 1 Day	0.1618	Russell 3000
2/8/2019 0:00	Russell 1000 Index TR  - Net Dividends 1 Day	0.1061	Russell 1000
2/8/2019 0:00	Russell 3000 Index TR  - Net Dividends 1 Day	0.1024	Russell 3000
2/7/2019 0:00	Russell 1000 Index TR  - Net Dividends 1 Day	-0.8789	Russell 1000
2/7/2019 0:00	Russell 3000 Index TR  - Net Dividends 1 Day	-0.8736	Russell 3000
2/6/2019 0:00	Russell 1000 Index TR  - Net Dividends 1 Day	-0.2146	Russell 1000
2/6/2019 0:00	Russell 3000 Index TR  - Net Dividends 1 Day	-0.2093	Russell 3000
2/5/2019 0:00	Russell 1000 Index TR  - Net Dividends 1 Day	0.4751	Russell 1000
2/5/2019 0:00	Russell 3000 Index TR  - Net Dividends 1 Day	0.4535	Russell 3000
2/4/2019 0:00	Russell 1000 Index TR  - Net Dividends 1 Day	0.6927	Russell 1000
2/4/2019 0:00	Russell 3000 Index TR  - Net Dividends 1 Day	0.7174	Russell 3000
2/1/2019 0:00	Russell 1000 Index TR  - Net Dividends 1 Day	0.1693	Russell 1000
2/1/2019 0:00	Russell 3000 Index TR  - Net Dividends 1 Day	0.1701	Russell 3000
1/31/2019 0:00	Russell 1000 Index TR  - Net Dividends 1 Day	0.874	Russell 1000
1/31/2019 0:00	Russell 3000 Index TR  - Net Dividends 1 Day	0.8719	Russell 3000
1/30/2019 0:00	Russell 1000 Index TR  - Net Dividends 1 Day	1.542	Russell 1000
1/30/2019 0:00	Russell 3000 Index TR  - Net Dividends 1 Day	1.5068	Russell 3000
1/29/2019 0:00	Russell 1000 Index TR  - Net Dividends 1 Day	-0.1412	Russell 1000
1/29/2019 0:00	Russell 3000 Index TR  - Net Dividends 1 Day	-0.1411	Russell 3000
1/28/2019 0:00	Russell 1000 Index TR  - Net Dividends 1 Day	-0.7293	Russell 1000
1/28/2019 0:00	Russell 3000 Index TR  - Net Dividends 1 Day	-0.722	Russell 3000
1/25/2019 0:00	Russell 1000 Index TR  - Net Dividends 1 Day	0.9034	Russell 1000
1/25/2019 0:00	Russell 3000 Index TR  - Net Dividends 1 Day	0.9295	Russell 3000
1/24/2019 0:00	Russell 1000 Index TR  - Net Dividends 1 Day	0.2111	Russell 1000
1/24/2019 0:00	Russell 3000 Index TR  - Net Dividends 1 Day	0.2464	Russell 3000
1/23/2019 0:00	Russell 1000 Index TR  - Net Dividends 1 Day	0.1854	Russell 1000
1/23/2019 0:00	Russell 3000 Index TR  - Net Dividends 1 Day	0.1561	Russell 3000
1/22/2019 0:00	Russell 1000 Index TR  - Net Dividends 1 Day	-1.4173	Russell 1000
1/22/2019 0:00	Russell 3000 Index TR  - Net Dividends 1 Day	-1.4371	Russell 3000
1/18/2019 0:00	Russell 1000 Index TR  - Net Dividends 1 Day	1.3088	Russell 1000
1/18/2019 0:00	Russell 3000 Index TR  - Net Dividends 1 Day	1.2894	Russell 3000
1/17/2019 0:00	Russell 1000 Index TR  - Net Dividends 1 Day	0.7793	Russell 1000
1/17/2019 0:00	Russell 3000 Index TR  - Net Dividends 1 Day	0.7856	Russell 3000
1/16/2019 0:00	Russell 1000 Index TR  - Net Dividends 1 Day	0.2723	Russell 1000
1/16/2019 0:00	Russell 3000 Index TR  - Net Dividends 1 Day	0.3002	Russell 3000
1/15/2019 0:00	Russell 1000 Index TR  - Net Dividends 1 Day	1.0508	Russell 1000
1/15/2019 0:00	Russell 3000 Index TR  - Net Dividends 1 Day	1.0374	Russell 3000
1/14/2019 0:00	Russell 1000 Index TR  - Net Dividends 1 Day	-0.535	Russell 1000
1/14/2019 0:00	Russell 3000 Index TR  - Net Dividends 1 Day	-0.5691	Russell 3000
1/11/2019 0:00	Russell 1000 Index TR  - Net Dividends 1 Day	-0.0073	Russell 1000
1/11/2019 0:00	Russell 3000 Index TR  - Net Dividends 1 Day	0.0031	Russell 3000
1/10/2019 0:00	Russell 1000 Index TR  - Net Dividends 1 Day	0.4742	Russell 1000
1/10/2019 0:00	Russell 3000 Index TR  - Net Dividends 1 Day	0.4735	Russell 3000
1/9/2019 0:00	Russell 1000 Index TR  - Net Dividends 1 Day	0.4934	Russell 1000
1/9/2019 0:00	Russell 3000 Index TR  - Net Dividends 1 Day	0.5201	Russell 3000
1/8/2019 0:00	Russell 1000 Index TR  - Net Dividends 1 Day	1.0201	Russell 1000
1/8/2019 0:00	Russell 3000 Index TR  - Net Dividends 1 Day	1.0555	Russell 3000
1/7/2019 0:00	Russell 1000 Index TR  - Net Dividends 1 Day	0.7888	Russell 1000
1/7/2019 0:00	Russell 3000 Index TR  - Net Dividends 1 Day	0.8601	Russell 3000
1/4/2019 0:00	Russell 1000 Index TR  - Net Dividends 1 Day	3.4373	Russell 1000
1/4/2019 0:00	Russell 3000 Index TR  - Net Dividends 1 Day	3.4597	Russell 3000
1/3/2019 0:00	Russell 1000 Index TR  - Net Dividends 1 Day	-2.3845	Russell 1000
1/3/2019 0:00	Russell 3000 Index TR  - Net Dividends 1 Day	-2.3456	Russell 3000
1/2/2019 0:00	Russell 1000 Index TR  - Net Dividends 1 Day	0.0703	Russell 1000
1/2/2019 0:00	Russell 3000 Index TR  - Net Dividends 1 Day	0.1041	Russell 3000
12/31/2018 0:00	Russell 1000 Index TR  - Net Dividends 1 Day	0.8843	Russell 1000
12/31/2018 0:00	Russell 3000 Index TR  - Net Dividends 1 Day	0.8784	Russell 3000
12/28/2018 0:00	Russell 1000 Index TR  - Net Dividends 1 Day	-0.0812	Russell 1000
12/28/2018 0:00	Russell 3000 Index TR  - Net Dividends 1 Day	-0.0391	Russell 3000
12/27/2018 0:00	Russell 1000 Index TR  - Net Dividends 1 Day	0.8361	Russell 1000
12/27/2018 0:00	Russell 3000 Index TR  - Net Dividends 1 Day	0.7881	Russell 3000
12/26/2018 0:00	Russell 1000 Index TR  - Net Dividends 1 Day	4.9688	Russell 1000
12/26/2018 0:00	Russell 3000 Index TR  - Net Dividends 1 Day	4.9686	Russell 3000
12/24/2018 0:00	Russell 1000 Index TR  - Net Dividends 1 Day	-2.6869	Russell 1000
12/24/2018 0:00	Russell 3000 Index TR  - Net Dividends 1 Day	-2.6342	Russell 3000
12/21/2018 0:00	Russell 1000 Index TR  - Net Dividends 1 Day	-2.0721	Russell 1000
12/21/2018 0:00	Russell 3000 Index TR  - Net Dividends 1 Day	-2.1061	Russell 3000
12/20/2018 0:00	Russell 1000 Index TR  - Net Dividends 1 Day	-1.5934	Russell 1000
12/20/2018 0:00	Russell 3000 Index TR  - Net Dividends 1 Day	-1.6017	Russell 3000
12/19/2018 0:00	Russell 1000 Index TR  - Net Dividends 1 Day	-1.5301	Russell 1000
12/19/2018 0:00	Russell 3000 Index TR  - Net Dividends 1 Day	-1.5649	Russell 3000
12/18/2018 0:00	Russell 1000 Index TR  - Net Dividends 1 Day	0.0185	Russell 1000

I appreciate any help in solving the issue!

Thanks

[Anonymous]

HI @Anonymous ,

Your formula not works on my side(it says syntax error), can you please fix it?

BTW, I think you need to add a variable summary table to calculate each row result, then you can use sumx function to get cumulative based on that table.

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @Anonymous ,

The code works on my side.

Any ideas on how do I script the requirement of withdrawing money every year?

Thanks

[Anonymous]

Hi @Anonymous ,

Can you please share a pbix file for test?

Regards,

Xiaoxin Sheng