Monthly growth rate for the multi-year period

[PeterChu]

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.

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.

[Ashish_Mathur · Accepted solution]

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.

[PeterChu]

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.

[Ashish_Mathur]

You are welcome.

[arvindyadav]

HI @Ashish_Mathur ,

How to do a weekly growth rate in power bi?

[Ashish_Mathur]

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.

[Greg_Deckler]

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

[PeterChu]

Thank you, I will pay attention next time.