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.
Hi Experts
I am actually working on Revenue Expansion and Contraction, My data table contain below column:
Invoice Date / User ID / Invoice Amount / Invoice ID
The result will be like this:
The month of Invoice Expantion
Jan 2019 $$$$$$
Feb 2019 $$$$$$
The month of Invoice Contraction
Jan 2019 $$$$$$
Feb 2019 $$$$$$
we can drill down the user id too, It will help us who downgrade the service.
Solved! Go to Solution.
I have resolved my issue by using 5 steps
Step 1 : For Previous Month Invoice
Last Month Invoice = CALCULATE(SUM('Invoice Data'[ConvertedToUSD]),DATEADD('Invoice Data'[InvoiceDate].[Date],-1,MONTH))
Step 2 : For Monthly Revenue Jump Net
Per Month Revenue Jump = SUM('Invoice Data'[ConvertedToUSD])-[Last Month Invoice]
Step 3 : For Revenue Contraction (This give you the amount of those recurring customers who paid current month invoice too)
Recurring Downgrade = -(SUMX(VALUES('Invoice Data'[UserID]),MIN(BLANK(),'Invoice Data'[Per Month Revenue Jump])))
So, it means that all those customers who paid the invoice 1st-time last month and not paying any invoice in the current month were excluded in Total for that I have added Step 5. To get this you need to get expansion First.
Step 4 : For Revenue Expansion
Revenue Upgrade = SUMX(VALUES('Invoice Data'[UserID]),MAX(BLANK(),'Invoice Data'[Per Month Revenue Jump]))
Step 5 : For those customers who paid 1st time in last month and not paying any invoice this month
Last Month Revenue Churned = (Revenue Upgrade]-[Recurring Downgrade])-[Per Month Revenue Jump]
I am not pro but it resolved my Problem
Regards
And thanks for the Help
The information you have provided is not making the problem clear to me. Can you please explain with an example. If possible please share a sample pbix file after removing sensitive information.
Thanks
Thanks for the reply let me re-collaborate my self.
I Just want to calculation the Upgrade and downgrade of User Invoices each month.
I have an Invoice table from 2012 to 2019 containing (Invoice Date / User ID / Invoice Amount / Invoice ID) every month we generate invoices for our customers. If want to check jump each month, I Use formula:
Total Jump in Revenue = Sum (Invoice Amount) - Calculate (Sum(Invoice Amount),Filter (Invoice Table, Previousmonth(Invoice date)
This give me net result.
But the actual case we are in recurring business and there must be a downgrage and upgrade from a customer. So I Just want to see how many (in term of customers) and how much (In term of revenue) reduce and upgrade each month
Results should be Like this:
Date Amount Downgrade
Jan 2019 $XXXXXXXX
Feb 2019 $XXXXXXXX
March 2019 $XXXXXXXX
April 2019 $XXXXXXXX
You may create calculate column like pattern below:
Upgrade_Revenue =
VAR Current_Month =
MONTH ( Table[Month] )
VAR Previous_Month =
MONTH ( EOMONTH ( Current_Month, -1 ) )
VAR Current_Month_Revenue =
SUMX ( FILTER ( Table, MONTH ( Table[Date] ) = Current_Month ), Table[Revenue] )
VAR Previous_Month_Revenue =
SUMX (
FILTER ( Table, MONTH ( Table[Date] ) = Previous_Month ),
Table[Revenue]
)
RETURN
Current_Month_Revenue - Previous_Month_Revenue / Previous_Month_Revenue
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for the reply, I have checked your formula.
but divding it think this will give me growth in %, but I need every month downgrade amount and upgrade amount. I know the for both downgrade and upgrade the formula will be different.
I have resolved my issue by using 5 steps
Step 1 : For Previous Month Invoice
Last Month Invoice = CALCULATE(SUM('Invoice Data'[ConvertedToUSD]),DATEADD('Invoice Data'[InvoiceDate].[Date],-1,MONTH))
Step 2 : For Monthly Revenue Jump Net
Per Month Revenue Jump = SUM('Invoice Data'[ConvertedToUSD])-[Last Month Invoice]
Step 3 : For Revenue Contraction (This give you the amount of those recurring customers who paid current month invoice too)
Recurring Downgrade = -(SUMX(VALUES('Invoice Data'[UserID]),MIN(BLANK(),'Invoice Data'[Per Month Revenue Jump])))
So, it means that all those customers who paid the invoice 1st-time last month and not paying any invoice in the current month were excluded in Total for that I have added Step 5. To get this you need to get expansion First.
Step 4 : For Revenue Expansion
Revenue Upgrade = SUMX(VALUES('Invoice Data'[UserID]),MAX(BLANK(),'Invoice Data'[Per Month Revenue Jump]))
Step 5 : For those customers who paid 1st time in last month and not paying any invoice this month
Last Month Revenue Churned = (Revenue Upgrade]-[Recurring Downgrade])-[Per Month Revenue Jump]
I am not pro but it resolved my Problem
Regards
And thanks for the Help
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |