Growth rate (Month Over Month)

[nomanzafar]

Hi,

I want to calculate growth rate for Sales, month over month in absolute & percentage so that i can present it in table & waterfall chart to reflect monthly growth.

The data fields are as follow!

Month i.e. Jan Feb etc

Year 

region

city

Sales

Every month is repeating becasue of multiple regions & cities. please help me in this regard.  

[v-qiuyu-msft]

Hi @nomanzafar,

You can create a measure like below: 

Per = var PreMonth = CALCULATE(SUM('Table1'[Sales]),FILTER(ALL('Table1'),'Table1'[Month]=MAX('Table1'[Month])-1 && 'Table1'[Year]='Table1'[Year]))
var CurrMonth=CALCULATE(SUM(Table1[Sales]),FILTER(Table1, 'Table1'[Year]='Table1'[Year] && 'Table1'[Month]='Table1'[Month]))
return 
DIVIDE(CurrMonth-PreMonth,PreMonth)

 

Best Regards,
Qiuyun Yu 

[nomanzafar]

Thanks for your response. Can you help me to address following error. for your referance, please find below the error detail & measure. 

Error Message:
MdxScript(Model) (13, 120) Calculation error in measure 'Hierarchy'[Per]: DAX comparison operations do not support comparing values of type Text with values of type Number. Consider using the VALUE or FORMAT function to convert one of the values.

Measure

Per = var PreMonth = CALCULATE(SUM('Fr Performance 2017'[GA Achi]),FILTER(ALL('Fr Performance 2017'),'Fr Performance 2017'[Month]=MAX('Fr Performance 2017'[Month])-1 && 'Fr Performance 2017'[year]='Fr Performance 2017'[Year]))
var CurrMonth=CALCULATE(SUM('Fr Performance 2017'[GA Achi]),FILTER('Fr Performance 2017', 'Fr Performance 2017'[Year]='Fr Performance 2017'[Year] && 'Fr Performance 2017'[Month]='Fr Performance 2017'[Month]))
return
DIVIDE(CurrMonth-PreMonth,PreMonth)

[kristofferwest]

I don't know if you sitll have this problem, but I just ran into the same problem.

Solution: In stead of referencing a "month" collumn with a text, try to reference a month collum with a numeric value e.g. Jan = 1

Hope it helps 🙂

[kristofferwest]

I have a question on the code:

VAR PreMonth = CALCULATE('Fact Sales'[Revenue ACT] ;

FILTER(ALL('Dim Date') ; 'Dim Date'[Month Order] = MAX('Dim Date'[Month Order])-1 &&

'Dim Date'[Year] = 'Dim Date'[Year]))

VAR CurrMonth = CALCULATE('Fact Sales'[Revenue ACT] ;

FILTER('Dim Date' ; 'Dim Date'[Date].[Year] = 'Dim Date'[Date].[Year] &&

'Dim Date'[Month Order] = 'Dim Date'[Month Order]))

RETURN

DIVIDE(CurrMonth - PreMonth ; PreMonth)

When i place this on a visual with the calendar dimension on the axis I am not able to drill to the month level, the measure stops working at the quater level.

If I change the measure to:

VAR PreDay = CALCULATE('Fact Sales'[Revenue ACT] ;

FILTER(ALL('Dim Date') ; 'Dim Date'[Day] = MAX('Dim Date'[Day])-1 &&

'Dim Date'[Year] = 'Dim Date'[Year]))

VAR CurrDay = CALCULATE('Fact Sales'[Revenue ACT] ;

FILTER('Dim Date' ; 'Dim Date'[Day] = 'Dim Date'[Day] &&

'Dim Date'[Date].[Year] = 'Dim Date'[Date].[Year]))

RETURN

DIVIDE(CurrDay - PreDay ; PreDay)

Then it works on the month level. So is the correct formula the last one?