Forum Discussion
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 get a picture of what i am trying to do.
- The % Change is ((Month/previous month)-1)
- "Count of Month" is a measure.
| Month | Count of Month | Difference | % Change |
| Jan-17 | 39224 | ||
| Feb-17 | 56580 | 17356 | 44% |
| Mar-17 | 76302 | 19722 | 35% |
| Apr-17 | 148072 | 71770 | 94% |
| May-17 | 367138 | 219066 | 148% |
| Jun-17 | 155014 | -212124 | -58% |
| Jul-17 | 227602 | 72588 | 47% |
| Aug-17 | 80799 | -146803 | -64% |
| Sep-17 | 275734 | 194935 | 241% |
| Oct-17 | 202661 | -73073 | -27% |
| Nov-17 | 134777 | -67884 | -33% |
| Dec-17 | 141884 | 7107 | 5% |
Thanks in advance
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
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"
9 Replies
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
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"
- Hi, I have something similar. I want to show difference between this two table on a month with graphics preview. In this case I want to show the difrerence for each parameters graphically (visual). Can you help me ?
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)
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.
