cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

Growth rates (Month Over Month) formula

Hello all,

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!

11 REPLIES 11
Highlighted
Super User VII

Re: Growth rates (Month Over Month) formula

Hi,

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.

Thanks,

P

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Highlighted
Resident Rockstar

Re: Growth rates (Month Over Month) formula

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 ?

Highlighted
Microsoft

Re: Growth rates (Month Over Month) formula

@dmitrpav

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:

Regards,

Highlighted
Frequent Visitor

Re: Growth rates (Month Over Month) formula

You are absolutely correct.

I need to find the right solution for what you defined "This Overall Revenue"

Highlighted
Frequent Visitor

Re: Growth rates (Month Over Month) formula

Dear @v-sihou-msft,

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?

Highlighted
Microsoft

Re: Growth rates (Month Over Month) formula

@dmitrpav

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?

Regards,

Highlighted
Super User VII

Re: Growth rates (Month Over Month) formula

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.

Thanks,

P

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Highlighted
Frequent Visitor

Re: Growth rates (Month Over Month) formula

@v-sihou-msft

Exactly, I would like to have the previous month total to appear on the first day of a month only.

Kind regards,

D

Highlighted
Frequent Visitor

Re: Growth rates (Month Over Month) formula

Would it be possible you send to me this file you mention? Thanks

Announcements

Microsoft Ignite

This will be a conference that you do not want to miss!

Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors