Recursive Dax Formula

[TusharM09]

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...

PBIX file link - https://aparindltd-my.sharepoint.com/:u:/g/personal/wealth_mgt_apar_com/ERtQtpYsQOlIs6fOl88fCIUBdJRn...

Regards,

Tushar M.

[danextian]

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.

[TusharM09]

@danextian ......

[TusharM09]

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.

[danextian]

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.