Calculating Sales first year, second year and so on

CorvanDalfzen · June 28, 2018, 2:18pm

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

sam.mckay · June 28, 2018, 11:08pm

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?

CorvanDalfzen · June 29, 2018, 6:44am

Hi Sam,

Hahaha , 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

sam.mckay · July 1, 2018, 11:25pm

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.

CorvanDalfzen · July 3, 2018, 2:46pm

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.

Cheers,

Cor

sam.mckay · July 4, 2018, 4:47am

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

sam.mckay · July 4, 2018, 4:57am

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.

CorvanDalfzen · July 4, 2018, 2:43pm

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

sam.mckay · July 6, 2018, 1:00am

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

sam.mckay · July 6, 2018, 4:18am

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

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

sam.mckay · July 6, 2018, 7:02am

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

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

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

CorvanDalfzen · July 7, 2018, 7:25am

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.

Greetings from The Netherlands,

Cor

CorvanDalfzen · July 13, 2018, 7:44am

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

sam.mckay · July 16, 2018, 2:55am

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.