Forum Discussion

nomanzafar's avatar
nomanzafar
New Member
8 years ago

Growth rate (Month Over Month)

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.  

5 Replies

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    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's avatar
      nomanzafar
      New Member
      8 years ago

      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's avatar
        kristofferwest
        Icon for Advocate I rankAdvocate I
        7 years ago

        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 :)

    • astha129's avatar
      astha129
      Icon for Microsoft Employee rankMicrosoft Employee
      1 year ago

      which visual you have chosen?