topic Re: Growth rate calculation in DAX Commands and Tips

Growth rate calculation

alearner — Sat, 12 Oct 2024 04:27:38 GMT

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.

Re: Growth rate calculation

alearner — Sat, 12 Oct 2024 11:40:52 GMT

Experts any help would greatly be appreciated.

Re: Growth rate calculation

Dangar332 — Sat, 12 Oct 2024 14:57:38 GMT

Hi, alearner

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

Re: Growth rate calculation

alearner — Sun, 13 Oct 2024 07:33:56 GMT

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

Re: Growth rate calculation

alearner — Sun, 13 Oct 2024 13:46:39 GMT

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.