FV Schedule

[Abhilash_P]

Hello Everyone,

I have excel file with below columns

Year	Returns
2019	6.90%
2020	0.70%
2021	39.70%

Below excel formula is has been used 

FVSCHEDULE(1,B2:B4)^(1/3)-1

which return - 14.57%

How to create same DAX for the same 

Regards 

[v-jianboli-msft · Accepted solution]

Hi @Abhilash_P ,

Please try:

Measure =
VAR _a =
    ADDCOLUMNS ( 'Table', "R", [Returns] + 1 )
RETURN
    POWER ( PRODUCTX ( _a, [R] ), 1 / COUNTROWS ( _a ) ) - 1

Final output:

Best Regards,

Jianbo Li

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

[Abhilash_P]

Hello @v-jianboli-msft ,
Thanks for the reply,
Below is my data set 
PeerReturnsYear

Makena	-0.213	2009
TUCS > $5bn	-0.127	2009
KIC	0.173	2009
CIC	0.117	2009
70/30	0.261	2009
60/40	0.233	2009
Makena	0.141	2010
TUCS > $5bn	0.133	2010
KIC	0.08	2010
70/30	0.108	2010
60/40	0.102	2010
Makena	0.183	2011
TUCS > $5bn	0.215	2011
KEVA	-0.017	2011
KIC	-0.042	2011
70/30	-0.034	2011
60/40	-0.021	2011
Makena	0	2012
TUCS > $5bn	0.017	2012
KEVA	0.129	2012
KIC	0.115	2012
CIC	0.106	2012
70/30	0.126	2012
60/40	0.115	2012
Makena	0.078	2013
TUCS > $5bn	0.12	2013
KEVA	0.075	2013
CIC	0.093	2013
70/30	0.147	2013
60/40	0.121	2013
Makena	0.149	2014
TUCS > $5bn	0.17	2014
ABP	0.145	2014
KEVA	0.087	2014
70/30	0.031	2014
60/40	0.028	2014
Makena	0.067	2015
TUCS > $5bn	0.035	2015
ABP	0.027	2015
KEVA	0.048	2015
70/30	-0.024	2015
60/40	-0.025	2015
Makena	-0.02	2016
TUCS > $5bn	0.012	2016
AIMCo	0.058	2016
ABP	0.095	2016
PGGM	0.117	2016
KEVA	0.074	2016
70/30	0.063	2016
60/40	0.057	2016
Makena	0.107	2017
TUCS > $5bn	0.126	2017
AIMCo	0.093	2017
ABP	0.076	2017
KEVA	0.077	2017
70/30	0.188	2017
60/40	0.171	2017
Makena	0.1	2018
TUCS > $5bn	0.086	2018
AIMCo	0.023	2018
ABP	-0.023	2018
KEVA	-0.03	2018
70/30	-0.068	2018
60/40	-0.06	2018
Makena	0.035	2019
TUCS > $5bn	0.065	2019
OMERS	0.119	2019
AIMCo	0.106	2019
ABP	0.168	2019
PGGM	0.187	2019
KEVA	0.128	2019
70/30	0.205	2019
60/40	0.186	2019
Makena	0.228	2020
TUCS > $5bn	0.125	2020
OMERS	-0.027	2020
AIMCo	0.025	2020
ABP	0.062	2020
PGGM	0.056	2020
KEVA	0.047	2020
70/30	0.147	2020
60/40	0.14	2020
70/30	0.112	2021
60/40	0.088	2021
Top 5 endowments	-0.224	2009
70/30	-0.202	2009
60/40	-0.171	2009
Top 5 endowments	0.12	2010
70/30	0.1	2010
60/40	0.093	2010
Top 5 endowments	0.208	2011
70/30	0.241	2011
60/40	0.221	2011
Top 5 endowments	0.031	2012
70/30	-0.034	2012
60/40	-0.025	2012
Top 5 endowments	0.122	2013
70/30	0.107	2013
60/40	0.087	2013
Top 5 endowments	0.181	2014
70/30	0.182	2014
60/40	0.166	2014
Top 5 endowments	0.094	2015
70/30	-0.016	2015
60/40	-0.024	2015
Top 5 endowments	0.002	2016
70/30	0.001	2016
60/40	0.014	2016
Top 5 endowments	0.123	2017
70/30	0.121	2017
60/40	0.1	2017
Top 5 endowments	0.124	2018
70/30	0.079	2018
60/40	0.07	2018
Top 5 endowments	0.067	2019
70/30	0.061	2019
60/40	0.061	2019
Top 5 endowments	0.062	2020
70/30	0.032	2020
60/40	0.034	2020
Top 5 endowments	0.429	2021
70/30	0.274	2021
60/40	0.236	2021
Makena		2009
Makena		2010
Makena		2011
Makena		2012
Makena		2013
Makena		2014
Makena		2015
Makena		2016
Makena		2017
Makena		2018
Makena		2019
Makena		2020
Makena		2021
Council	0.183	2009
Council	0.15	2010
Council	0.032	2011
Council	0.106	2012
Council	0.117	2013
Council	0.098	2014
Council	0.024	2015
Council	0.13	2016
Council	0.213	2017
Council	0.002	2018
Council	0.197	2019
Council	0.216	2020
Council	0.258	2021
Council	-0.194	2009
Council	0.104	2010
Council	0.248	2011
Council	-0.014	2012
Council	0.075	2013
Council	0.202	2014
Council	0.065	2015
Council	0.001	2016
Council	0.17	2017
Council	0.129	2018
Council	0.069	2019
Council	0.007	2020
Council	0.397	2021
CDPQ	0.1	2009
OTTP	0.13	2009
ilmarinen	0.158	2009
Norway	0.256	2009
CDPQ	0.136	2010
OTTP	0.143	2010
ilmarinen	0.108	2010
Norway	0.096	2010
CIC	0.117	2010
CDPQ	0.04	2011
OTTP	0.112	2011
ilmarinen	-0.04	2011
Norway	-0.025	2011
CIC	-0.043	2011
CDPQ	0.096	2012
OTTP	0.13	2012
ilmarinen	0.075	2012
Norway	0.13	2012
CDPQ	0.131	2013
OTTP	0.109	2013
ilmarinen	0.098	2013
Norway	0.159	2013
KIC	0.089	2013
CDPQ	0.12	2014
OTTP	0.118	2014
ilmarinen	0.068	2014
Norway	0.076	2014
KIC	0.038	2014
CIC	0.055	2014
CDPQ	0.091	2015
OTTP	0.13	2015
ilmarinen	0.06	2015
Norway	0.027	2015
KIC	-0.032	2015
CIC	-0.03	2015
CDPQ	0.076	2016
OTTP	0.042	2016
ilmarinen	0.048	2016
Norway	0.069	2016
KIC	0.042	2016
CIC	0.062	2016
CDPQ	0.093	2017
OTTP	0.097	2017
ilmarinen	0.072	2017
Norway	0.137	2017
KIC	0.163	2017
CIC	0.059	2017
CDPQ	0.042	2018
OTTP	0.025	2018
ilmarinen	-0.014	2018
Norway	-0.061	2018
KIC	-0.038	2018
CIC	-0.024	2018
CDPQ	0.104	2019
OTTP	0.104	2019
ilmarinen	0.118	2019
Norway	0.2	2019
KIC	0.152	2019
CIC	0.174	2019
CDPQ	0.077	2020
OTTP	0.086	2020
ilmarinen	0.071	2020
Norway	0.109	2020
KIC	0.135	2020
CIC		2020
CDPQ		2021
OTTP		2021
ilmarinen		2021
Norway		2021
KIC		2021
Yale	-0.246	2009
MIT	-0.171	2009
Harvard	-0.273	2009
Upenn	-0.157	2009
Stanford	-0.259	2009
Princeton	-0.235	2009
CalSTRS	-0.25	2009
Futures Fund	-0.042	2009
Yale	0.089	2010
MIT	0.102	2010
Harvard	0.11	2010
Upenn	0.126	2010
Stanford	0.144	2010
Princeton	0.147	2010
CalSTRS	0.122	2010
Futures Fund	0.106	2010
Yale	0.219	2011
MIT	0.179	2011
Harvard	0.214	2011
Upenn	0.19	2011
Stanford	0.224	2011
Princeton	0.219	2011
CalSTRS	0.231	2011
Futures Fund	0.128	2011
Yale	0.047	2012
MIT	0.08	2012
Harvard	-0.001	2012
Upenn	0.016	2012
Stanford	0.01	2012
Princeton	0.031	2012
CalSTRS	0.018	2012
Futures Fund	0.021	2012
Yale	0.125	2013
MIT	0.111	2013
Harvard	0.113	2013
Upenn	0.144	2013
Stanford	0.122	2013
Princeton	0.117	2013
CalSTRS	0.138	2013
Futures Fund	0.154	2013
Yale	0.202	2014
MIT	0.192	2014
Harvard	0.154	2014
Upenn	0.175	2014
Stanford	0.168	2014
Princeton	0.196	2014
CalSTRS	0.187	2014
Futures Fund	0.143	2014
Yale	0.115	2015
MIT	0.132	2015
Harvard	0.058	2015
Upenn	0.074	2015
Stanford	0.07	2015
Princeton	0.115	2015
CalSTRS	0.048	2015
Futures Fund	0.154	2015
Yale	0.034	2016
MIT	0.008	2016
Harvard	-0.02	2016
Upenn	-0.014	2016
Stanford	-0.004	2016
Princeton	0.008	2016
CalSTRS	0.014	2016
Futures Fund	0.048	2016
Yale	0.113	2017
MIT	0.143	2017
Harvard	0.081	2017
Upenn	0.143	2017
Stanford	0.131	2017
Princeton	0.125	2017
CalSTRS	0.134	2017
Futures Fund	0.087	2017
Yale	0.123	2018
MIT	0.135	2018
Harvard	0.1	2018
Upenn	0.129	2018
Stanford	0.113	2018
Princeton	0.142	2018
CalSTRS	0.09	2018
Futures Fund	0.093	2018
Yale	0.057	2019
MIT	0.088	2019
Harvard	0.065	2019
Upenn	0.065	2019
Stanford	0.065	2019
Princeton	0.062	2019
CalSTRS	0.068	2019
Futures Fund	0.115	2019
Yale	0.068	2020
MIT	0.083	2020
Harvard	0.073	2020
Upenn	0.034	2020
Stanford	0.056	2020
Princeton	0.056	2020
CalSTRS	0.039	2020
Futures Fund	-0.009	2020
Yale	0.402	2021
MIT	0.555	2021
Harvard	0.336	2021
Upenn	0.411	2021
Stanford	0.401	2021
Princeton	0.469	2021
CalPERS		2015
CalPERS		2016
CalPERS		2017
CalPERS		2018
CalPERS		2019
CalPERS		2020
CalSTRS		2021
CalPERS		2021
Futures Fund		2021

ther is year selection in the slicer if i selected 2022 it has to calcualte for previous 3 years (2022,2021,2020) dynamically.

Regards
Abhilash.P

[Samarth_18]

Hi @Abhilash_P ,

I am afraid if dax having any function as FVSCHEDULE but it having FV function you can refer below url:-

https://docs.microsoft.com/en-us/dax/fv-function-dax

For more financial functions you can refer below url:-

https://docs.microsoft.com/en-us/dax/financial-functions-dax 

Thanks,

Samarth 

[Abhilash_P]

@Samarth_18  Thanks for sharing no direct formulae available any indirect refrence (combination of 2 dax)..?