cancel
Showing results for
Search instead for
Did you mean:
Regular Visitor

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

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.

1 ACCEPTED SOLUTION
Super User I

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

Owen Auger

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

My Blog
Connect on Twitter
Connect on LinkedIn
4 REPLIES 4
Super User I

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

Owen Auger

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

My Blog
Connect on Twitter
Connect on LinkedIn
Helper III

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.

Super User III

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Super User I

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

Owen Auger

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

My Blog
Connect on Twitter
Connect on LinkedIn

## Helpful resources

Announcements

#### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

#### Check it out!

Click here to read more about the July 2021 Updates

#### Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors