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

      • kristofferwest's avatar
        kristofferwest
        Icon for Advocate I rankAdvocate I
        7 years ago

        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?
  • astha129's avatar
    astha129
    Icon for Microsoft Employee rankMicrosoft Employee
    1 year ago

    which visual you have chosen?