cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Growth rate (Month Over Month)

Hello all,

 

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

 

Power BI MoM issue.PNG

 

 

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Growth rate (Month Over Month)

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.
1.PNG2.PNG


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

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.
4.PNG


Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Highlighted
Microsoft
Microsoft

Re: Growth rate (Month Over Month)

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.
1.PNG2.PNG


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

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.
4.PNG


Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Anonymous
Not applicable

Re: Growth rate (Month Over Month)

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

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors