Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dmitrpav
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
v-sihou-msft
Employee
Employee

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

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?

 

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

 

@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

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

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

 

@Cristina101083

 

I believe that message for me, send the file where? Shoot me pvt. message and I will send it to you.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

Baskar
Resident Rockstar
Resident Rockstar

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 ?

@Baskar, thank you for your reply!

 

You are absolutely correct.

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

 

 

parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

@parry2k

 

I sent you a PM with a request.  Best,  CJS

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.