how to calculate growth rate

[Anonymous]

Hi everyone, I have a table contains: Product, Month, Year, Sales revenue columns,

and I want to calculate the growth rate for product by month and by year. I have searched some topics and found that LOOKUPVALUE function is popular but I cannot write the formula to solve my problem. 

Can anyone help me with it? Thanks!

[Anonymous]

Hello,

I need to calculate the fastest growth rate. I have a dataset which is as follows

this contains population and GDP values for all states of USA from the year 1997 onwards.
I have calculated the GDP Growth and Population Growth Rates using measures, and now want to calculate the fastest growth rates among all states. Please can someone help.

[Ashish_Mathur]

Hi,

On a sample dataset (which can be pasted in an MS Excel file), please share the exact result that you are expecting.

[Anonymous]

Year	Alabama	Alaska	Arizona	Arkansas
1997	104087.4	25744.8	132418	59865.6
1998	109404.3	24170.4	143392.4	62442.5
1999	115030.9	24732.6	155344.5	66816.7
2000	119242.4	26778.3	164611.6	68770
2001	122449.3	28420.4	171474.2	70601.4
2002	127792.3	29605.3	179413.3	73950.4
2003	133739.2	31799.7	192751.2	78218.8
2004	146524.7	34970.7	205420.8	84624.5
2005	155970.4	39774.8	228231.2	89610
2006	163853.6	44435.3	248230.7	94384.5
2007	169678.4	48972.6	262322	96948.2
2008	172686.8	54752.4	259366.7	98955.3
2009	168348	49585.9	243929.4	97058.9
2010	174753	52882.5	248210	101690
2011	181349.8	56259.9	257947.8	106085.4
2012	186299	57670.1	268288.8	108745.2

This is a part of my dataset which has YoY GDP data for the 50 states of USA. Here I want to calculate the fastest GDP growth rate among these states. I have calculated the GDP growth rate in power BI by creating a measure, below is the code

GDP Growth =

var FirstYear = CALCULATE(MIN('Report Data'[Year]))

var LatestYear = CALCULATE(MAX('Report Data'[Year]))

var GDPFirstYear = CALCULATE('Report Data'[GDP Measure], 'Report Data'[Year] = FirstYear)

var GDPLatestYear = CALCULATE('Report Data'[GDP Measure], 'Report Data'[Year] = LatestYear)

var Diff = GDPLatestYear - GDPFirstYear

return DIVIDE(Diff, GDPFirstYear, 0)

 I want to calculate the max growth rate among these 50 states, i.e which states among these grew the fastest.

[Ashish_Mathur]

Hi,

Do you simply want to calculate the highest growth rate between first and last year or do you also want to know which was that state?

[Anonymous]

Hello,

Yes, I want to know the state as well. Thank you.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

Add a calculated column in data view mode (not measure, not in Query Editor mode) to original table:

Revenue LM =
LOOKUPVALUE (
    MonthGrowth[Month Revenue],
    MonthGrowth[Division], MonthGrowth[Division],
    MonthGrowth[Year], IF ( MonthGrowth[Month] = 1, MonthGrowth[Year] - 1, MonthGrowth[Year] ),
    MonthGrowth[Month], IF ( MonthGrowth[Month] = 1, 12, MonthGrowth[Month] - 1 )
)

Best regards,
Yuliana Gu

[Greg_Deckler]

You might want to create a date column from your year and month columns and then you should be able to use the time intelligence functions such as PREVIOUSMONTH

https://msdn.microsoft.com/en-us/library/ee634758.aspx

and SAMEPERIODLASTYEAR

https://msdn.microsoft.com/en-us/library/ee634972.aspx

[Anonymous]

Hi, thanks for your suggestions and I tried SAMPLEPERIODLASTYEAR. But the result is like

It has the total last year revenue but I may need the sales for every month. Can you help me with it?

[Ashish_Mathur]

Hi,

There's some work one will need here.  To use the Date/time intelligence functions, one must have:

1. A date column in the base data; and
2. A calendar table
3. Two two tables should be related
4. The time fields in your visuals should ideally be dragged from the calendar table

It is only then that the date/time intelligence functions will work.  Share your file to get more specific help.

[Anonymous]

Hi, thanks for your tips. Sorry that I am not very clear about it. Do you mean that I need create a calendar table and use the Date column in the calendar table as the axis in the visual? Because currently I only have one table like:

[Ashish_Mathur]

Yes, that is what i meant.

[Anonymous]

Hi Mathur,

Your solution is great. However, I can't draw % grow vs last month on chart, as below. How can I fix it? Thank you in advance

[Ashish_Mathur]

Hi,

Share the download link of your PBI file.