Hello - I need help to correctly classify our sales for commission calcs. The execs want to reward sales made to “New” customers and “New” product lines. They define new as being 12 months from the date of the first sale of that customer for that product line (there are 5 prod lines). All sales after the initial 12 months is to be tagged as existing.
My idea was to write DAX to create a results table which considers the “Product” of the item and the “Account Ref” of the customer and returns the first date that a transaction appears for that combination in the revenue transactions table.
From these date starting points I can then work out 365 days after which will then allow me to compare the sales date to this start and end date and add a column to my revenue table for either
- if it is equal to or within the date range it will be classified as “NEW” (business)
- if it is after the date range it will be classified as “EXISTING” (business)
NB - Sales in my model are linked to items by item number and to customers by account ref
Does that make sense?
This is what I have tried so far…
Results = ALL(DimSFCustomers[Account Ref]) _This created me a one column table_ Account Name = LOOKUPVALUE(DimSFCustomers[Alias], DimSFCustomers[Account Ref], [Account Ref]) _This succeeded in getting the account names into column 2_ Start Date = CALCULATE(MIN('2 Revenue'[Date]), FILTER(RELATEDTABLE('2 Revenue'),'2 Revenue'[Account Ref] = 'Results'[Account Ref])) _This returned the first date for that customer but I don't know how to add the extra filter for item product_
So now I am stuck and hope someone can help.
I have attached a workbook showing how I imagined the results table would look and the column of “new/existing labels” for the revenue table.
Many thanks Lizzie
DAX Problem for DNA.xlsx (16.7 KB)