Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Calculate Sales first year, second year etc.

Hello everyone,

 

Can someone help me with the dax-formulas to calculate the sales in the first, second year and so on?

 

 

The table:

InvoicedateInvoicenumberCustomernumber Amount excl VAT Year
1-1-20071100                        10,002007
25-6-20072200                        20,002007
17-12-20073300                        30,002007
9-6-20084400                        40,002008
1-12-20085500                        50,002008
25-5-20096600                        60,002009
16-11-20097700                        70,002009
10-5-20108100                        80,002010
1-11-20109200                        90,002010
25-4-201110300                      100,002011
17-10-201111400                      110,002011
9-4-201212500                      120,002012
1-10-201213600                      130,002012
25-3-201314700                      140,002013
16-9-201315100                      150,002013
10-3-201416200                      160,002014
1-9-201417300                      170,002014
23-2-201518400                      180,002015
17-8-201519500                      190,002015
8-2-201620600                      200,002016
1-8-201621700                      210,002016

 

For example,

Customer 100 had a salesamount in his first year of 10 and his fourth year of 80, customer 700 had a salesamount in his first year of 70 and in his 8th year of 210.

 

Thanks in advance,

 

With kind regards,


Cor

 

Zubair_Muhammad

 

 

Need Help

4 Replies

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    Anonymous,

     

    You may use DAX below to add a calculated column.

    Column =
Table1[Year]
    - MINX (
        FILTER ( Table1, Table1[Customernumber] = EARLIER ( Table1[Customernumber] ) ),
        Table1[Year]
    )
    + 1
    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      v-chuncz-msft

      Zubair_Muhammad

       

      Hi Sam and Zubair,

       

      Thanks for your replies!

       

      I was expecting the next result:

       

       

      The result I get in PBI was:

       

      What goes wrong?

       

      Can you both also help me with the averages for each year? I mean the average in the first year for the 2007-customers is 20, for the 2008-customers is the average in their first year 45 and so on.

       

      Thanks in advance,

       

      Cor