Growth rate (Month Over Month)

[dmitrpav]

Hello all,

I want to make growth rates for Revenue Month over Month ( = percent change) for this table:

So, I need to sum up values for all dates taking into account country and source, the sum up all values for all dates in the month, and finally calculate the percentage change.

The problem is that every date occurs 28 times, sice there are 14 countires and 2 sources. So, the solution could be -  previous month total appear ones (e.g. on the first day of month). Could you please help me with this?

I will be very grateful for your help!

[v-yuezhe-msft · Accepted solution]

Hi @dmitrpav,

You can perform the following steps  to achieve the requirement.

1. Duplicate your table in Query Editor or Power BI Desktop.

2. In the duplicated table, create two custom columns(Monthnumber and Year) as follows.

3. Group the revenue as shown in the following screenshot.


4.Create two calculated columns using the following formulas.

difference = IF(Table1[Country]=LOOKUPVALUE(Table1[Country],Table1[Index],Table1[Index]-1) && Table1[Source]=LOOKUPVALUE(Table1[Source],Table1[Index],Table1[Index]-1) && Table1[Year]=LOOKUPVALUE(Table1[Year],Table1[Index],Table1[Index]-1),Table1[month revenue]-LOOKUPVALUE(Table1[month revenue],Table1[Index],Table1[Index]-1),0)

%change = DIVIDE(Table1[difference],LOOKUPVALUE(Table1[month revenue],Table1[Index],Table1[Index]-1),0)

5. Create a Matrix visual to show result.

Thanks,
Lydia Zhang

[Anonymous]

Hello,

I am trying to make a growth trend analysis, but unable to get that. Issue is pritty similar to this issue. 
I have a Calendar Table and Sales Table. My Sample Duration is 24 months. Example, Jan2017 till Dec2018. Calendar table contains all 730 days. My Sales Table has Daily sales data. Now I need to project a 6 months Trend Chart with MOM growth.

I need to use a Month Slicer, to select month (Jan 2018, Feb 2018 or Any) that is fixed single selection only and plot below points;
1. Selected Months Sales Volume($ value)
2. Previous Months Sales Volumn ($ Value)
3. Growth Rate (%) = (Selected Month Sales Volume - Previous Months Sales Volumn) / Previous Months Sales Volumn
4. Growth Rate Trend Line Chart > A Bar Chart to project Last 6 months Growth Rate.

I have tryed some tricks from other guru's blog,
Like,
1. Previous Months Sales Volumn ($ Value) = CALCULATE (SUM('Sales Table'[Selected Months Sales Volume]),PREVIOUSMONTH('Calendar'[Calendar Date]))
2. Growth Rate (%) =
VAR _CurrentMonthVolume = SUM('Sales Table'[Selected Months Sales Volume])
VAR _LastMonthVolume = 'Sales Table'[Previous Months Sales Volumn]
VAR _Diff = _CurrentMonthVolume - _LastMonthVolume
RETURN
DIVIDE(_Diff,_LastMonthVolume,0)
3. Growth Rate Trend = CALCULATE ('Sales Table'[Growth Rate (%)],DATESINPERIOD ( 'Calendar'[Calendar Date], MAX ( 'Calendar'[Calendar Date] ),-6, MONTH ))

Unfortunately, Nothing is working while plotting these No. in a table. Growth Trend is showing all 100% and 0%. but in Card all Numbers are comming correct.

Would be greatful if you can help me on this. 

Regards,

Sukhomoy