Growth rate calculation

[alearner]

Hello, I am trying to compare the Growth Rate for a Set of Mutula Fund Schemes. I have a table which has the NAV for the last couple of years and the table name is NAV.

As a first step what I have done is forward filled the dates in the NAv table where on Saturday, Sunday or Holidays there would be blank NAV. So I would be required to forward fill the NAV to have the Saturday, Sunday or Holiday also to have data. 

I have used the below DAX to forward fill the NAV. Please refer to the attached pbix file https://www.dropbox.com/scl/fi/1degmqsbnz75456p3bqvo/NAV_GrowthRate.pbix?rlkey=1l137rw55h08lglbp5jci... 

GrowthRate =

  var minDate = MIN('Date'[Date])

  var maxDate = MAX('Date'[Date])

  var nonnullDate = CALCULATE(MAX('Date'[Date]),

                    FILTER(ALL(NAV),

                    NAV[navDate] <= MAX('Date'[Date]) &&

                    (NAV[navValue]) <> 0

                        ))

   VAR nonblanknav = CALCULATE(

                        sum(NAV[navValue]),

                        'Date'[Date] = nonnullDate)

  return nonblanknav

What I am interested is getting a line chart which shows the Groth Rate as per the date filter applied so that I can compare the Growth rate of the dirrerent Mutual Fund schemes from the date range selected something like below. 

Please let me know of any query and I would be more than happy to clarify your questions. 

[alearner]

@Dangar332 using the below Measure I have been able to make sure that for all the dates which is either a Saturday, Sunday or a Holiday I have a NAV value.  

GrowthRate =

  var minDate = MIN('Date'[Date])

  var maxDate = MAX('Date'[Date])

  var nonnullDate = CALCULATE(MAX('Date'[Date]),

                    FILTER(ALL(NAV),

                    NAV[navDate] <= MAX('Date'[Date]) &&

                    (NAV[navValue]) <> 0

                        ))

   VAR nonblanknav = CALCULATE(

                        sum(NAV[navValue]),

                        'Date'[Date] = nonnullDate)

  return nonblanknav

The place where I am stuck now is that I would like to have the growth rate calculated day on day basis so that I can compare which Mutual Fund scheme is the best performing and which the worst. So I my start date is Jan1st 2024 then the Growth rate would be zero. For 2nd Jan it would be (2ndJan NAV-1stJan NAV)/1stJan NAV and for 3rdJan it would be  (3rdJan NAV-1stJan NAV)/1stJan NAV and so on by the Respective Mutual Fund Schememe Name for the date slicer selected.  I have also attached a test pbix file https://www.dropbox.com/scl/fi/1degmqsbnz75456p3bqvo/NAV_GrowthRate.pbix?rlkey=1l137rw55h08lglbp5jci... which has the sample date with slicer

[alearner · Accepted solution]

@Dangar332 I achieved what I wanted to implement however I had to take the help of Python to achieve it. I used Pyth to achieve what I was implementing via the below code.

GrowthRate =

  var minDate = MIN('Date'[Date])

  var maxDate = MAX('Date'[Date])

  var nonnullDate = CALCULATE(MAX('Date'[Date]),

                    FILTER(ALL(NAV),

                    NAV[navDate] <= MAX('Date'[Date]) &&

                    (NAV[navValue]) <> 0

                        ))

   VAR nonblanknav = CALCULATE(

                        sum(NAV[navValue]),

                        'Date'[Date] = nonnullDate)

  return nonblanknav.

I imported the data in PowerBI and then using the below code I achieved the final result:

GrowthRate =

    var strtdt = CALCULATE(MIN('Date'[Date]), ALLSELECTED('Date'))

    var enddt = MAX('Date'[Date])

    var mindateNAV = CALCULATE(

                     SUM(ALL_NAV[navValue]),

                     'Date'[Date]= strtdt

    )

    VAR change = SUM(ALL_NAV[navValue])-mindateNAV

RETURN DIVIDE(change,mindateNAV)

The Graph: As you can see I can do a comparison on what the returns have been over the specified time duration selected in slicer.

[alearner]

Experts any help would greatly be appreciated. 

[Dangar332]

Hi, @alearner 

Can you Explain where you stuck ,From above description i can't get your problem.