Forum Discussion
Recursive Dax Formula
Dear Expert,
I am new to Power BI, read some articles on Recursive formulas in BI, but found that it is a bit of a hac.
I want to create a formula shown in yellow highlighted in Excel as same as in Bi table.
I am sending a link to my sample Excel file as well as pbix file.
if someone of you can help then it will be very appreciated.
Excel file link - https://aparindltd-my.sharepoint.com/:x:/g/personal/wealth_mgt_apar_com/EUHxlpNjlYhEoVuUA--AptsBLv45_MfppSi1yO_hHSGpCw?e=KO14uo
PBIX file link - https://aparindltd-my.sharepoint.com/:u:/g/personal/wealth_mgt_apar_com/ERtQtpYsQOlIs6fOl88fCIUBdJRnCx7elmmM9a5B3792Cg?e=tJARzp
Regards,
Tushar M.
4 Replies
Hi TusharM09 ,
Please see the attached and take note of the following:
Formula is only for runing sum of Factorial Corpus- it is unclear whether you want to do it for the units as welll as it hardcoded in your sample file but a similar formula can be used.
I created two measures - one uses the fact dates and the other a separate dates table. It is always a good practice to use a separate dates table for time intelligence calculation. You might see unexpected results if you start adding another dimension column to your visual if a separates table is not used.
Hey danextian,
you are on the correct path so happy.
I have been working on this for the last two weeks and now found some hope.
Ans. to your question...
1) yes I want the formula on unit Nav too ( NAV value / FC of the date)
2) The formula of FC is divided into three parts = (Total Corpus of current date / Unit Nav of Last date)+FC of last date
3) in my main model I have used a separate calendar table
4) I have hardcoded it as i don't know how to write it,
Regards,
Tushra M.
You can try this formula:
Unit Nav (Measure) = DIVIDE ( SUM ( Sheet1[NAV Value ] ), [Factoral Corpus (Calendar)] )Please note that the corpus numbers in your pbix is 10x than in your Excel file.
