Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PeterChu
Frequent Visitor

Monthly growth rate for the multi-year period

Hi Everyone,

I am trying to do a monthly growth rate for the multi-year period in PowerBi DeskTop. 

 

1. Here is what my raw data looks like and the date interval is from 2017-11-14 to2018-03-30.

RowData.png

 

2. I created 5 measurements.

 

Year = YEAR('dailydata'[Date].[Date]) 

Month = MONTH('dailydata'[Date].[Date])

previousmonth = CALCULATE(SUM('dailydata'[Revenue]),FILTER(ALLSELECTED('dailydata'), [Month] = max([Month])-1))

currentmonth = CALCULATE(SUM('dailydata'[Revenue]),FILTER(ALLSELECTED('dailydata'),[Month]  = MAX([Month])))

Month% = [previousmonth]/[currentmonth] 

 

3. I presented the measurements in the data sheet,but the 2018-01 value is empty.

   How doi slove this problem? Thank yoy all.

123.png

 

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

 

Try this

 

  1. In the image that you have pasted, the Date column is a text column.  Change that to a Date column in the Query Editor first.  Click on Close and Apply
  2. Go to Modelling > New Table and enter this formula to Create a Calendar Table.  Name this Table as Calendar = CALENDAR(MIN(Data[Date]),MAX(Data[Date]))
  3. Create a relatioship from the Date column in your data to the Date column in the Calendar Table
  4. In the Calendar Table, create these calculated column formulas = YEAR(Calendar[Date]) and =MONTH(Calendar[Date])
  5. In your visual drag the Year and Month from the Calendar Table
  6. Write these measures

Total Revenue = SUM(Data[Revenue])

Month (%) = CALCULATE([Total Revenue],PREVIOUSMONTH(Calendar[Date]))/[Total Revenue]

 

Hope this helps.

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

7 REPLIES 7
Ashish_Mathur
Super User
Super User

Hi,

 

Try this

 

  1. In the image that you have pasted, the Date column is a text column.  Change that to a Date column in the Query Editor first.  Click on Close and Apply
  2. Go to Modelling > New Table and enter this formula to Create a Calendar Table.  Name this Table as Calendar = CALENDAR(MIN(Data[Date]),MAX(Data[Date]))
  3. Create a relatioship from the Date column in your data to the Date column in the Calendar Table
  4. In the Calendar Table, create these calculated column formulas = YEAR(Calendar[Date]) and =MONTH(Calendar[Date])
  5. In your visual drag the Year and Month from the Calendar Table
  6. Write these measures

Total Revenue = SUM(Data[Revenue])

Month (%) = CALCULATE([Total Revenue],PREVIOUSMONTH(Calendar[Date]))/[Total Revenue]

 

Hope this helps.

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thank you @Ashish_Mathur.

 

This solution indeed works,but i created a new measure 『LastMonthRevenue = CALCULATE([TotalTurnover],PREVIOUSMONTH(Calendar[Date]))』 and changed 『Month (%) = CALCULATE([Total Revenue],PREVIOUSMONTH(Calendar[Date]))/[Total Revenue]』 into 『Month (%) = IF([LastMonthRevenue ] = BLANK(),"NA",([TotalTurnover] / [LastMonthRevenue ])-1) because original solution showed message.

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

HI @Ashish_Mathur ,

 

How to do a weekly growth rate in power bi?

Hi,

Share some data and show the expected result.  More importantly, you will have to first define week in the Calendar Table i.e. which date falls in each week.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Greg_Deckler
Super User
Super User

Can you paste the data from your first screen shot as text that can be copied? In the PBIX file, go to the middle tab on the left and then right click the table and choose Copy Table. Paste this into Excel and truncate as necessary.

 

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thank you, I will pay attention next time.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.