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