Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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

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...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

     

    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.

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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
)
Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
5 years ago

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.