Calculate total $ customer invoice growth compared to last month only from growing customers

[SteveCnz]

Hello, I thought this problem would be simple but has not been and no example I could find (Dax Patterns, EnterpriseDNA, PPPs love you all) seems to deal with this scenario.

We run a subscription business and invoice our customers once per month.

How do I calculate with DAX the total $ growth in customer invoicing this month compared to last month only for customers whose total invoicing this month is greater than their total last month? (This is easy on a customer by customer basis, but I want it on a total basis).  I have a standard total invoicing table with unique customer id linked to a calendar table by invoice date.

Any help would be much appreciated.

[OwenAuger · Accepted solution]

Hi @SteveCnz

Here's one way of doing it.

I'm going to assume you have a table called Invoices with columns Customer ID, Invoice Date, Amount.

As per your description, I'll assume Invoices[Invoice Date] is related to 'Calendar'[Date].

Then I would create the following measures (some of which you probably already have):

Invoice Amount =
SUM ( Invoices[Amount] )

Invoice Amount Month Ago =
CALCULATE (
    [Invoice Amount],
    DATEADD ( 'Calendar'[Month], -1, MONTH )
)

Invoice Amount Growth =
[Invoice Amount] - [Invoice Amount Month Ago]

Invoice Amount Growth Increases Only =
SUMX ( 
    VALUES ( Invoices[Customer ID] ),
    MAX ( BLANK(), [Invoice Amount Growth] )
)

The last measure iterates through Customers and sums the growth only for the Customers who have increased.

Regards,

Owen

[shanipowerbi]

I have use the same fomula  for downgrade  and its work for me but the problem I find that by using SumX(Values(Customer ID),Min(Blank(), [Invoice growth])) is I am not getting last month new users who did not pay invoice in current month.

[Ashish_Mathur]

Hi,

Does this measure work?

=SumX(CALCULATETABLE(Values(Customer ID),DATESBETWEEN(Calendar[Date],EDATE(MIN(Calendar[Date]),-1),MAX(Calendar[Date]))),Min(Blank(), [Invoice growth]))

[OwenAuger]

@shanipowerbi 

Ideally we should have a separate Customer dimension table to iterate over. This would ensure that all customers are considered whether or not they are present in the current filter context.

So the measure should be something like:

=
SUMX (
    VALUES ( Customer[Customer ID] ),
    MIN ( BLANK (), [Invoice growth] )
)