cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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:

 

Power BI MoM issue.PNG 

 

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






Did I answer your question? Mark my post as a solution.

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





Highlighted
Resident Rockstar
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
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:

 

123.PNG

 

Regards,

Highlighted
Frequent Visitor

Re: Growth rates (Month Over Month) formula

@Baskar, thank you for your reply!

 

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,

Thank you for your solution!

Now I am closer to the final desired outcome.

 

Capture2.JPG

 

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
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
Super User VII

Re: Growth rates (Month Over Month) formula

Hi @dmitrpav

 

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






Did I answer your question? Mark my post as a solution.

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

 

Helpful resources

Announcements

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