Forum Discussion

Jorgast's avatar
Jorgast
Resolver II
8 years ago
Solved

Previous Month Compare

Hi Everyone,   I have the month and the "count of the month" but what i am trying to do, is find the difference and the % change against the previous month.   I created the data in Excel so you g...
Need Help
  • ricardocamargos's avatar
    ricardocamargos
    8 years ago

    Hi Jorgast,

     

    You can try these measures:

     

    _Previous Month = CALCULATE(SUM(Table1[Count of Month]); PREVIOUSMONTH(DIM_DATA[Date]))

    _Diff = SUM(Table1[Count of Month]) - [_Previous Month]

    _% Change = IF(ISBLANK(DIVIDE(SUM(Table1[Count of Month]);  [_Previous Month]; 0)) = TRUE(); BLANK();  DIVIDE(SUM(Table1[Count of Month]);  [_Previous Month]; 0) - 1)

     

    Be aware it's important to have a date dimension in order to use time intelligence functions.

     

    Thanks,

     

    Ricardo

  • Jorgast's avatar
    Jorgast
    8 years ago

    ricardocamargos

     

    How would that work if the count of month is measure?

    MEASURE:  Count of Month = COUNT(Table[Month])

    COLUMN:   Month = Format(Table[Month], "MMM - YYYY"

ricardocamargos's avatar
ricardocamargos
Continued Contributor
8 years ago

Hi Jorgast,

 

You can try these measures:

 

_Previous Month = CALCULATE(SUM(Table1[Count of Month]); PREVIOUSMONTH(DIM_DATA[Date]))

_Diff = SUM(Table1[Count of Month]) - [_Previous Month]

_% Change = IF(ISBLANK(DIVIDE(SUM(Table1[Count of Month]);  [_Previous Month]; 0)) = TRUE(); BLANK();  DIVIDE(SUM(Table1[Count of Month]);  [_Previous Month]; 0) - 1)

 

Be aware it's important to have a date dimension in order to use time intelligence functions.

 

Thanks,

 

Ricardo

  • Jorgast's avatar
    Jorgast
    Resolver II
    8 years ago

    ricardocamargos

     

    How would that work if the count of month is measure?

    MEASURE:  Count of Month = COUNT(Table[Month])

    COLUMN:   Month = Format(Table[Month], "MMM - YYYY"

    • ricardocamargos's avatar
      ricardocamargos
      Continued Contributor
      8 years ago

      Hi Jorgast,

       

      I didn't understand you question, sorry about that.

       

      Ricardo

      • Jorgast's avatar
        Jorgast
        Resolver II
        8 years ago

        Hi @ricardocamargos

         

        I was trying to create the formula for _Previousmonth but i am getting hung up on the Calculate(Sum(count of month), Previousmonth(Table[date]). For my report the count of month is a measure. Can that be done using a measure or would the count of month need to be a column?