Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Jorgast
Resolver II
Resolver II

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
ricardocamargos
Continued Contributor
Continued Contributor

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

@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
toni14
Helper I
Helper I

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 ?

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

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

@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
Continued Contributor
Continued Contributor

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

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

Hi @Jorgast,

 

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

 

Ricardo

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?

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

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.