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"

Jorgast's avatar
Jorgast
Resolver II
7 years ago

ricardocamargos

 

Ricardo had the right idea. In your case you just want to show the difference between 2 tables, so you would stop at step #2

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

toni14's avatar
toni14
Helper I
7 years ago

Hi Jorgast,

 

Thank you for your help. I tried this but doesn't work. It shows me that DIM_DATA cannot be found. I will send you some screenshots.

  • Jorgast's avatar
    Jorgast
    Resolver II
    7 years ago

    toni14 

     

    You are missing a date table or some sort of date. The PReviousMonth Function is uses a date in order to determine what the previos month was. In your case you are using a time data which is different.