I want to make growth rates for Revenue Month over Month ( = percent change) for this table:
The problem is that every date occurs 28 times, sice there are 14 countires and 2 sources.
So, I need to sum up values for all dates taking into account country and source, the sum up all values for all dates in the month, and finally calculate the percentage change.
Here is the formula that I tried to use, but it doesn't work properly.
Revenue % Change = VAR RevenueLastMonth = CALCULATE ( SUM ( Tables[Revenue] ); FILTER(Tables; Tables[Month] = ( EARLIER ( Tables[Month] ) - 1 ) && Tables[Country] = EARLIER ( Tables[Country] ) && Tables[Source] = EARLIER ( Tables[Source] ) ) ) RETURN IF ( ISBLANK ( RevenueLastMonth ); 0; ( Tables[Revenue] - RevenueLastMonth ) / RevenueLastMonth )
I will be very grateful for your help!
I have pbix file which i can share with you, it has calculations that shows how to calculate % change from previous month/quarter/year. Unfortuantely formu doesn't allow to upload/attach file, if you are ok to share your email (may be thru private message), I can send you the file.
For my assumptiom from your requirement .
Do u want the percentage of Month by Country and Source elevel ?
Example = ( [Current revenue] - [Overall Month Revenue] ) / [Overall Month Revenue]
This Overall Revenue = Sum of Revenue at all day of current month with current country and source combination .
This is correct dude ?
In this scenario, what are the values in Tables[Month]? If it's just the month part of the date, it will return incorrect result since month number will repeat in every year. So you should also add the Year column as condition in your FILTER() function. Then it will show the previous month total on each row.
Revenue % Change = VAR RevenueLastMonth = CALCULATE ( SUM ( Tables[Revenue] ); FILTER(Tables; Tables[Month] = ( EARLIER ( Tables[Month] ) - 1 ) && Tables[Year] = EARLIER ( Tables[Year] ) && Tables[Country] = EARLIER ( Tables[Country] ) && Tables[Source] = EARLIER ( Tables[Source] ) ) ) RETURN IF ( ISBLANK ( RevenueLastMonth ); 0; ( Tables[Revenue] - RevenueLastMonth ) / RevenueLastMonth )
See my sample below:
Thank you for your solution!
Now I am closer to the final desired outcome.
The sum of previous month Revenue is shown for the each date of the month.
If it had appeared only once, the % Change formula would give me the right result.
Do you know how to make it?
This is a calculated column, the calculation in formula will be resolved on each row. What do you mean "appear only once"? Do you want the previous month total appear on the first day of month?
As mentioned earlier I can send you sample pbix file which has all the calculations and I'm sure it will be helpful. I did already sent you private message for me to send you the file. Let me know if you are still interested.
We're excited to announce our first cross-community 'Can You Solve These?' challenge!
Visit our Community Blog for articles, guides, and information created by fellow community members.
Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.