Calculating Sales first year, second year and so on


#1

Hi everyone,

Can someone help me with the DAX-formulas for calculating Total Sales per customer for the first year, second year and so on?

The table with invoices:

Invoicedate Invoicenumber Customernumber Amount excl VAT Year
1-1-2007 1 100 10,00 2007
25-6-2007 2 200 20,00 2007
17-12-2007 3 300 30,00 2007
9-6-2008 4 400 40,00 2008
1-12-2008 5 500 50,00 2008
25-5-2009 6 600 60,00 2009
16-11-2009 7 700 70,00 2009
10-5-2010 8 100 80,00 2010
1-11-2010 9 200 90,00 2010
25-4-2011 10 300 100,00 2011
17-10-2011 11 400 110,00 2011
9-4-2012 12 500 120,00 2012
1-10-2012 13 600 130,00 2012
25-3-2013 14 700 140,00 2013
16-9-2013 15 100 150,00 2013
10-3-2014 16 200 160,00 2014
1-9-2014 17 300 170,00 2014
23-2-2015 18 400 180,00 2015
17-8-2015 19 500 190,00 2015
8-2-2016 20 600 200,00 2016
1-8-2016 21 700 210,00 2016

Measures:
FirstInvoice =
CALCULATE (
MIN ( Invoices[Invoicedate] );
ALL ( ‘Model Dim_Date’ )
)

Total excl VAT = 

SUM(Invoices[Amount excl VAT])

Thanks in advance,

With kind regards,

Cor van Dalfzen


#2

Hi Cor,

Shouldn’t this just be super easy.

Sum the invoice column and then place it in a matrix with customer and year. I’m confused as to why this would be difficult and maybe I’m not fully understanding what you are requiring.

What am I missing?


#3

Hi Sam,

Hahaha :slight_smile: , it isn’t that easy, let me explain.
I want an insight of the total sales from customers in the first year, the second year and so on. Also I would like to know what the averages for each year are, see photo.

Totals%20and%20averages

I have a measure Firstinvoice and I think that I have to make a measure with Datesbetween or Datesinperiod in the formula to calculate the period between the date of the first invoice and 1 year, 2 years etc. and then calculate the total sales in this period. Can you explain how I have to do it? Can you also look at the formula for the averages?

Thanks in advance,

Cheers,

Cor


#4

I’m really sorry but I’m just not that clear still on what’s required here.

You’re saying you need to calculate totals is year1 year 2 year3.

The image you’ve supplied already has the results, so it this what you are already calculating? or is this what you want?

To me what you have supplied should be able to be calculated so simply??

I’m sorry based on the explanation I’m just not getting it right now?

You’re saying you need total sales per customer right in the first year? So what is wrong with a simple sum and then placing that against the year context. Simple.

Then for the averages you’ll want to use AVERAGEX in some way.

See below for examples on this, hopefully this helps.


#5

Hi Sam,

I hope I can makes things clear:

First I want to compare customers with their Total Sales in the first, second, third and so on year they become a customer with us.

For example:
Customer 100 became our customer on 1-1-2007 and the Sales in his first year was 10,00, in his second year he had no Sales. In his fourth year he had Sales of 80. I’ve calculated this in Excel and the image is the result in Excel. I would like to do the calculations etc. in Power BI.

Another challenge are the averages. I want to compare years with each other.
The average sales of customers who became a customer in 2007 is 20 (Total invoices 2007 is 60, divided by 3 customers (100, 200, 300). The result of these customers in the first year is 20.

Do you understand it? If not, i will give it another try. :slight_smile:

Cheers,

Cor


#6

Ok so you actually want to difference between years from what I understand now?

But still this isn’t some identifiable logic here.

Do you want to show to difference between last year results, or do you want to show the difference from one year to a different time period.

It’s still not clear the actual logic you would need at any particular context.

I’m reading this

First I want to compare customers with their Total Sales in the first, second, third and so on year they become a customer with us.

Want is the comparison logic though that is the issue now? When you say compare do you mean versus last year, or the year before or the last 3 years? It’s important because the formula would be different for all cases.

Then I’m reading this

Customer 100 became our customer on 1-1-2007 and the Sales in his first year was 10,00, in his second year he had no Sales. In his fourth year he had Sales of 80

Still what is the required logic around comparison here? It’s just not clear sorry. What you are describing around this is a very simple sum?

Sorry it’s still not clear to me what you require


#7

Just another thing…this honestly shouldn’t be difficult to answer. I just need to clearly understand the requirement.

Time comparison work shouldn’t be to difficult as the time intelligence function make it so. So it’s just more clarity around the specific calculation that is needed.


#8

Hi Sam,

See photo’s:

Challenge 1:

Challenge 2:
Challenge%202

Hopefully you will understand the challenges, if not, please let me know.

Cheers,

Cor


#9

Ok right I think I understand now. I’ll have to do some testing here on this one.

Now that I fully understand it, it isn’t so easy.

The very first thing you have to do is actually bring in a column which contains year-1 year-2 and so on. As this currently doesn’t exist in your data.

It’s a very interesting one though, so I look forward to trying to work out a solution here

Sam


#10

Ok so here are my ideas for a solution - I have looked to mock up the entire scenario in a Power BI model.

First you need to get a column of data into you model. I’ve used enter data in this case

image

Now we have a column we can work with and we need to implement some logic here based on the raw data

First we need to work out the year from first purchase. This can be done with a formula like this

Year From First Purchase = 
SELECTEDVALUE( Invoices[Year] ) - 
    CALCULATE( MIN( Invoices[Year] ), ALLEXCEPT( Invoices, Invoices[Customernumber] ) )
         + 1

It’s important always to understand the context of the calculation. That’s why I always lay it out in table first like the below

Once you have that then you can work out the amount in this new context like so.

Formula here

Purchase Amount = 
VAR YearRef = SELECTEDVALUE( 'Year References'[Year Number], BLANK() )

RETURN
SUMX( 
    SUMMARIZE( Invoices, Invoices[Customernumber], Invoices[Year],
        "InvoiceAmt", [Total Invoices],
        "PurchaseYear", [Year From First Purchase] ),
            IF( [PurchaseYear] = YearRef, [InvoiceAmt], 0 ) )

There’s certainly a bit to this one. You have to think deeply about context here, especially in this last table. Because remember the year1 year2 etc has no relationship to anything in the model

It just sits out there and we need to use DAX to bring it all together.

All together though this is a very powerful technique


#11

For this next one, this is even more difficult.

I have spent quite a bit of time testing it.

I always recommend creating table here with all the information first. It help a lot when attempting to get the ultimate solution.

Like so

image

This has most of the information required here and now it’s about working through the table here to get it into the format that’s required.

Here’s the formula that got this working

Average Amount = 
VAR YearRef = SELECTEDVALUE( 'Year References'[Year Number], BLANK() )
VAR YearContext = SELECTEDVALUE( Invoices[Year], BLANK() )
VAR AvgAmount = 
 AVERAGEX( 
   SUMMARIZE( ALL( Invoices ), Invoices[Year], Invoices[Customernumber],
        "FirstPurchase", [First Year Purchase],
        "InvoiceAmt", [Total Invoices],                
        "PurchaseYear", [Year From First Purchase] ),
            IF( AND( [FirstPurchase] = YearContext, [PurchaseYear] = YearRef ), [InvoiceAmt], BLANK() ) )

RETURN
IF( ISBLANK( AvgAmount ), 0, AvgAmount )

Here are the results

image

Quite a bit to getting this working, no doubt!

I’ve attached the file here that I work on

Cor - July 2018.pbix (85.3 KB)

But good solution and great scenario. I’ll have to note this down and create some videos on it.

Good luck


#12

As always awesome solutions, Sam, thank you very much! I will dig a little deeper into it next week and maybe I will come back with another question. I’m looking forward to watch your videos on this topic. :slight_smile:

Greetings from The Netherlands,

Cor


#13

Hi Sam,

Your solution works perfect, thank you!

I have another question, you’ve entered data to make a table “Year Reference”.
I’ve made a calculated column by using the formula:

YearNumber = 
Invoices[Year]
    - MINX (
        FILTER ( Invoices; Invoices[Customernumber] = EARLIER ( Invoices[Customernumber] ) );
        Invoices[Year]
    )
    + 1

How can I change your formula “Average Amount” so that I will get the same results?

Cheers,

Cor


#14

I don’t believe you can change the formula at all with the additional column you have created. It needs to be separate table as I’ve outlined in the solution.

I don’t believe there’s any way to complete this otherwise.

You have to remember that your date table is already providing context in part of the calculation. If you attempted to use the same table to provide another context you would get into a lot of trouble.

Unless I’m not understanding exactly what you’re after, this is where things stand currently.