Calculate previous Month's % increase/decrease

[AG_2252]

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_Opened	Pri_Tax
1-Jul-23	Fraud
1-Jul-23	Payments/Funds/Transactions
1-Jul-23	Billing/Transaction Disputes
1-Jul-23	Billing/Transaction Disputes
1-Jul-23	Payments/Funds/Transactions
1-Jul-23	Billing/Transaction Disputes
1-Jul-23	Account Application/Decisioning
1-Aug-23	Billing/Transaction Disputes
1-Aug-23	Interest/Rates
1-Aug-23	Account Closure
1-Aug-23	Billing/Transaction Disputes
1-Aug-23	Product Features/Rewards
1-Aug-23	Account Closure
1-Aug-23	Product Features/Rewards
1-Aug-23	Account Closure
1-Aug-23	Product Features/Rewards
1-Aug-23	Billing/Transaction Disputes
1-Aug-23	Product Features/Rewards
1-Aug-23	Product Features/Rewards
1-Aug-23	Product Features/Rewards
1-Aug-23	Fraud
1-Aug-23	Account Application/Decisioning
1-Aug-23	Product Features/Rewards
1-Aug-23	Payments/Funds/Transactions
1-Aug-23	Billing/Transaction Disputes
1-Aug-23	Product Features/Rewards
1-Aug-23	Product Features/Rewards
1-Aug-23	Product Features/Rewards
1-Aug-23	Account Application/Decisioning
1-Aug-23	Account Closure
1-Sep-23	Statements/Documentation
1-Sep-23	Billing/Transaction Disputes
1-Sep-23	Service Delivery/Representative Handling
1-Sep-23	Billing/Transaction Disputes
1-Sep-23	Account Application/Decisioning

Example Pivot in excel:

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

[AG_2252]

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

Month	Account Closure	Fees	Transaction Dispute
Jul	554	34	542
Aug	500	35	759
Sep	832	40	974

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]

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.

[danextian]

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]

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

Jul	264	177	536	14	275	4	579	580	175	59	145	188	324	394	571	287	4572
Aug	286	154	556	29	305	15	900	713	174	71	148	181	281	495	579	337	5224
Sep	197	199	590	29	325	9	995	757	188	91	174	196	375	622	546	374	5667

[AG_2252]

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 Labels	Account Application/Decisioning	Account Closure	Billing/Transaction Disputes	Collections/Loss Mitigation/Default	Digital/Technology	Fraud	Interest/Rates	Payments/Funds/Transactions	Product Features/Rewards	Service Delivery/Representative Handling	Statements/Documentation	Grand Total
Jul	575	307	566	622	656	85	225	499	588	664	344	6409
Aug	511	253	706	964	805	106	215	476	649	670	403	7123
Sep	277	272	516	1037	810	113	213	504	728	611	424	6773
Grand Total	1363	832	1788	2623	2271	304	653	1479	1965	1945	1171	20305