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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
AG_2252
Frequent Visitor

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

5 REPLIES 5
AG_2252
Frequent Visitor

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

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

danextian_0-1700019989344.png

 

 

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.










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


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
danextian
Super User
Super User

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

 

danextian_0-1699943236149.png

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

 










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


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

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

 

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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