Forum Discussion

AG_2252's avatar
AG_2252
Frequent Visitor
2 years ago

Calculate previous Month's % increase/decrease

Hi All, New user to PowerBI trying to get a sense of things. I'm trying to make a Chart to show the difference between previous month data (3 months, Jul, Aug & Sep) to check a specific category ...
Need Help
danextian's avatar
danextian
Super User
2 years ago

Hi AG_2252 ,

Assuming that you're not using a separate dates table and all dates are beginning of the month, try these measures:

Previous Month's Value = 
CALCULATE (
    SUM ( Data[Value] ),
    FILTER (
        ALL ( Data[Date_Opened] ),
        Data[Date_Opened]
            = EDATE ( MAX ( Data[Date_Opened] ), -1 ) // date date one month ago from the current date
    )
)

Change = 
DIVIDE ( SUM ( Data[Value] ) - [Previous Month's Value], [Previous Month's Value] )

 

If this doesn't work, please provide  a complete sample data (with numbers, etc) and your expected result from that data.

 

  • AG_2252's avatar
    AG_2252
    Frequent Visitor
    2 years ago

    hI danextian 

    Thanks for your help. That didn't work for me unfortunately.. I have different days of each month, and the sample data just highlighted the 1st, because of the large number of data.

    Essentially, from the Pri_tax column, there are scenarios such as Product Features/Rewards,  Fees, Account closure etc. related complaints that I want to get a % increase or decrease of each scenario in the from one month to another. I've made a pivot below to show further. The ones where there have been a marked increase (30%) I want to have it highlighted in my chart to show the increase, as well as a marked decrease (-30%).

    Count of Date_Opened            
    Row LabelsAccount Application/DecisioningAccount ClosureBilling/Transaction DisputesCollections/Loss Mitigation/DefaultDigital/TechnologyFraudInterest/RatesPayments/Funds/TransactionsProduct Features/RewardsService Delivery/Representative HandlingStatements/DocumentationGrand Total
    Jul575307566622656852254995886643446409
    Aug5112537069648051062154766496704037123
    Sep27727251610378101132135047286114246773
    Grand Total1363832178826232271304653147919651945117120305
  • AG_2252's avatar
    AG_2252
    Frequent Visitor
    2 years ago

    Hi danextian 

    Thanks for your reply,  I tried the above but can't get it working.. Need some help.
    The dates are just samples, there are multiple days within each month and with different scenarios as well.


    There are different scenarios within the Pri_Tax Column (Account Application, Decisioning Account Closure Billing, Transaction Disputes Collections, Loss Mitigation, Default Digital/Technology, Fraud Interest/Rates Payments/Funds/Transactions Product Features/Rewards etc.) I need to group these and for each scenario see which one has had a marked increase or decrease (+/- 30%) from one month to another. I have made a sample pivot below:

    Spoiler
    Count of Pri_TaxColumn Labels                Row LabelsAccount Application/DecisioningAccount ClosureAccount MaintenanceAccount PrivacyBilling/Transaction DisputesBranch/ATMCollections/Loss Mitigation/DefaultDigital/TechnologyFeesFraudInterest/RatesMarketing/OffersPayments/Funds/TransactionsProduct Features/RewardsService Delivery/Representative HandlingStatements/DocumentationGrand TotalGrand Total74753016827290528247420505372214675659801511169699815463
    Jul264177536142754579580175591451883243945712874572
    Aug2861545562930515900713174711481812814955793375224
    Sep197199590293259995757188911741963756225463745667