Forum Discussion

alearner's avatar
alearner
Helper I
1 year ago
Solved

Growth rate calculation

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=1l137rw55h08lglbp5jciq3up&st=oe2v0pv9&dl=0 

 

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's avatar
    alearner
    1 year ago

    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.

4 Replies

    • Dangar332's avatar
      Dangar332
      Resident Rockstar
      1 year ago

      Hi, alearner 

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

  • alearner's avatar
    alearner
    Helper I
    1 year ago

    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's avatar
      alearner
      Helper I
      1 year ago

      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.