Previous Month Compare

[Jorgast]

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

[ricardocamargos · Accepted solution]

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

[toni14]

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 ?

[Jorgast]

@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]

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]

@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.

[ricardocamargos · Accepted solution]

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 · Accepted solution]

@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]

Hi @Jorgast,

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

Ricardo

[Jorgast]

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?

[ricardocamargos]

@Jorgast,

 Calculate(Sum(count of month), -> Should you use COUNT instead of SUM ? You just wanna know the quantity of the values, right ?

If you wanna use PREVIOUSMONTH, you should use date (date dimension).

Answering your question... you do it using measure, just be aware the functions you need.