cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SteveCnz
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
OwenAuger
Super User I
Super User I

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

 


Owen Auger

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

My Blog
Connect on Twitter
Connect on LinkedIn

View solution in original post

4 REPLIES 4
OwenAuger
Super User I
Super User I

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

 


Owen Auger

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

My Blog
Connect on Twitter
Connect on LinkedIn

View solution in original post

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.

 

 

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/

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

Owen Auger

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

My Blog
Connect on Twitter
Connect on LinkedIn

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

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

MBAS on Demand

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.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

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