cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Jorgast Member
Member

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.

 

 

MonthCount of MonthDifference% Change
Jan-1739224  
Feb-17565801735644%
Mar-17763021972235%
Apr-171480727177094%
May-17367138219066148%
Jun-17155014-212124-58%
Jul-172276027258847%
Aug-1780799-146803-64%
Sep-17275734194935241%
Oct-17202661-73073-27%
Nov-17134777-67884-33%
Dec-1714188471075%

 

Thanks in advance

2 ACCEPTED SOLUTIONS

Accepted Solutions
ricardocamargos Established Member
Established Member

Re: Previous Month Compare

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

View solution in original post

Jorgast Member
Member

Re: Previous Month Compare

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

View solution in original post

9 REPLIES 9
ricardocamargos Established Member
Established Member

Re: Previous Month Compare

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

View solution in original post

Jorgast Member
Member

Re: Previous Month Compare

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

View solution in original post

ricardocamargos Established Member
Established Member

Re: Previous Month Compare

Hi @Jorgast,

 

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

 

Ricardo

Jorgast Member
Member

Re: Previous Month Compare

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 Established Member
Established Member

Re: Previous Month Compare

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

 

 

toni14 Frequent Visitor
Frequent Visitor

Re: Previous Month Compare

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 Member
Member

Re: Previous Month Compare

@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 Frequent Visitor
Frequent Visitor

Re: Previous Month Compare

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.power_4.pngpower_3.pngpower_2.pngpower.png

Jorgast Member
Member

Re: Previous Month Compare

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 292 members 3,406 guests
Please welcome our newest community members: