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 under Pri_Tax that has seen a marked increase or decrease (30-40% range) and highlight in the chart. How would I go about this? I'm just a bit stumped at this part.


For reference this is a sample/snippet of the data I have:

Date_OpenedPri_Tax
1-Jul-23Fraud
1-Jul-23Payments/Funds/Transactions
1-Jul-23Billing/Transaction Disputes
1-Jul-23Billing/Transaction Disputes
1-Jul-23Payments/Funds/Transactions
1-Jul-23Billing/Transaction Disputes
1-Jul-23Account Application/Decisioning
1-Aug-23Billing/Transaction Disputes
1-Aug-23Interest/Rates
1-Aug-23Account Closure
1-Aug-23Billing/Transaction Disputes
1-Aug-23Product Features/Rewards
1-Aug-23Account Closure
1-Aug-23Product Features/Rewards
1-Aug-23Account Closure
1-Aug-23Product Features/Rewards
1-Aug-23Billing/Transaction Disputes
1-Aug-23Product Features/Rewards
1-Aug-23Product Features/Rewards
1-Aug-23Product Features/Rewards
1-Aug-23Fraud
1-Aug-23Account Application/Decisioning
1-Aug-23Product Features/Rewards
1-Aug-23Payments/Funds/Transactions
1-Aug-23Billing/Transaction Disputes
1-Aug-23Product Features/Rewards
1-Aug-23Product Features/Rewards
1-Aug-23Product Features/Rewards
1-Aug-23Account Application/Decisioning
1-Aug-23Account Closure
1-Sep-23Statements/Documentation
1-Sep-23Billing/Transaction Disputes
1-Sep-23Service Delivery/Representative Handling
1-Sep-23Billing/Transaction Disputes
1-Sep-23Account Application/Decisioning


Example Pivot in excel:

Appreciate it if anyone can help out :). Let me know if I need to explain it further. Cheers!!

Need Help

5 Replies

Replies have been turned off for this discussion
  • 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

       

  • 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. I was getting this error when trying to create a new measure:
    The syntax for 'Month' is incorrect. (DAX(Previous Month's Value = CALCULATE ( SUM ( Data[Value] ), FILTER ( ALL ( Data[Date_Opened] ), Data[Date_Opened] = EDATE ( MAX ( Data[Date_Opened] ), -1 ) // date
    Also, 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, the Pri_Tax column to include the below 3, out of around 20 categories

    MonthAccount ClosureFeesTransaction Dispute

    Jul

    		55434542
    Aug50035759
    Sep83240974


    Let me know if this makes sense.. Im not able to post any pictures or files so its a bit difficult from my end to include more data. 
    Thanks

    • danextian's avatar
      danextian
      Super User
      2 years ago

      Hi AG_2252 ,

       

      The formula I initially suggested for the previous month's value should be correct syntax-wise as I tested it myself. Your error is pointing to a separate measure which is Months. Either way, if your dates aren't just the start of the month, the formula will not work. I would suggest you created a separate dates table (either in DAX, M, or imported from an external file) to simplify time intelligence calculations. You can still use time intelligence functions even if there's no separate dates table but the result may not be as expected. With a separate dates table, you can use PREVIOUSMONTH function with the expected result.  Here's a sample formula and a sample pbix for your reference.

      Previous Month's Value = 
CALCULATE ( SUM ( Data[Value] ), PREVIOUSMONTH ( Dates[Date] ) )

       

       

      PS: Moving forward please post a sample data that actually represents the actual one (eg dates arent just he beginning of the month) as the proposed solution will be based on it.