Calculation of Revenue Expansion and Contraction

[shanipowerbi]

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.

[amitchandak]

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

[shanipowerbi]

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

[v-yuta-msft]

@shanipowerbi ,

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.

[shanipowerbi]

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.

[shanipowerbi · Accepted 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