Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
Solved! Go to 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
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"
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.
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.
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"
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?
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |