how do I create a calculated table that summarizes last 2 calendar year's sales and customer number

[bo_wang5]

Hi,

How do I create a calculated table that summaries last 2 calendar years sales in one column and unique customer codes in the second column?  I ultimately want to use this as a lookup table to identify new customer in the current calendar year.

Thanks.

[v-stephen-msft · Accepted solution]

Hi @bo_wang5 ,

I think you want to get a new calculated table based on your data source.

Sample data:

DateSalesSales Representative

11/1/2020	1	A
11/2/2020	2	A
1/1/2021	3	A
1/2/2021	4	A
1/3/2021	5	A
2/2/2021	6	A
3/2/2021	7	A
3/4/2021	8	A
11/1/2020	2	B
11/2/2020	2	B
1/1/2021	3	B
1/2/2021	3	B
1/3/2021	4	B
2/2/2021	4	B
3/2/2021	5	B
3/4/2021	5	B

Calculated table:

Table 2 = SUMMARIZE('Table',[Sales Representative],"Total Sales",SUM('Table'[Sales]))

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[daxer-almighty]

I suggest you not re-invent the wheel, especially if you don't know how to do it right. Instead, please do yourself a favour and follow the people who are the best in the field: New and returning customers – DAX Patterns

[bo_wang5]

Thanks for the link, very good article but goes over my head a lot, for example in the measure to determine new account date. instead of returning the 1st date that a customer bought something can you limit the measure to return the first date the customer bought within 2 years?  the Min function returns i think the first date in all history. I only want to know what their first order date is within last 2 years.

Date New Customer :=

CALCULATE (       

    MIN ( Sales[Order Date] ), -- The date of the first sale is the MIN of Order Date

    REMOVEFILTERS ( 'Date' )   -- at any time in the past

)

[daxer-almighty]

"I ultimately want to use this as a lookup table to identify new customer in the current calendar year." 

Does the article not let you do that correctly? If not, then you have different requirements and a different definition of what it means for a customer to be "new in the current frame of time."

To adjust the correct solution given in the article, you'd need to precisely define what you mean and what your data looks like.

[Ashish_Mathur]

Hi,

Create a Calendar Table which should have Year as a calculated column.  The Order Date column in your Sales table should have a relationship with the Date column in your Calendar Table.  To your Table/matrix visual, drag the Year column from the Calendar Table.  Write these measures:

Date of first interaction = calculate(MIN(Sales[Order Date]),datesbetween(Calendar[Date],minx(all(calendar),calendar[date])))

New Customers = countrows(filter(values(Sales[Customer Number]),min(calendar[date])>=[Date of first interaction])

Hope this helps.