Forum Discussion
Sales Representatives' First Year Sales
OK DAX geniuses out there - this one should hopefully be easy for you.
Goal: I want to total each sales representative's first year's sales.
I have 2 tables:
Table 1: SalesRep
ID Start Date
1 1/1/2018
2 2/5/2018
Table 2: Sales
OrderNumber Date Revenue ID
1000 1/3/2018 $1,000 1
1001 1/5/2018 $2,000 1
My 2 tables are linked via ID in 1 direction (sales reps single to sales many).
I would like to have a table that shows for each sales rep their first year's sales. Some sales representatives will have $0 sales.
End Result Table
ID First Year's Sales
1 $3,000
2 $0
My expectation is that a measure like this should be able to achieve what you are hoping for. There are some changes that might be required depending on your table structure and your intention on the measure. An ALL or ALLEXCEPT statement could be required in certain cases.
FirstYearSales = VAR startDate = LASTDATE('SalesRep'[Start Date]) VAR endDate = DATE(YEAR(startDate) + 1, MONTH(startDate), DAY(startDate) RETURN CALCULATE( SUM('Sales'[Revenue]), 'Sales'[Date] >= startDate, 'Sales'[Date] < endDate )Ross,
Thank you for pointing me in the right direction! I ended up needing a filter, too:
First Year Sales = CALCULATE(sum('Sales'[Revenue]),
filter(all(
'Sales'[date]),
'Sales'[Date] >= [VAR StartDate]&&'Sales'[Date]<[VAR EndDate]))
4 Replies
My expectation is that a measure like this should be able to achieve what you are hoping for. There are some changes that might be required depending on your table structure and your intention on the measure. An ALL or ALLEXCEPT statement could be required in certain cases.
FirstYearSales = VAR startDate = LASTDATE('SalesRep'[Start Date]) VAR endDate = DATE(YEAR(startDate) + 1, MONTH(startDate), DAY(startDate) RETURN CALCULATE( SUM('Sales'[Revenue]), 'Sales'[Date] >= startDate, 'Sales'[Date] < endDate )Ross,
Thank you for pointing me in the right direction! I ended up needing a filter, too:
First Year Sales = CALCULATE(sum('Sales'[Revenue]),
filter(all(
'Sales'[date]),
'Sales'[Date] >= [VAR StartDate]&&'Sales'[Date]<[VAR EndDate]))Nah, you don't need the filter, you can simply do this:
FirstYearSales = VAR startDate = LASTDATE('SalesRep'[Start Date]) VAR endDate = DATE(YEAR(startDate) + 1, MONTH(startDate), DAY(startDate) RETURN CALCULATE( SUM('Sales'[Revenue]), ALL('Sales'), 'Sales'[Date] >= startDate, 'Sales'[Date] < endDate )Thank you, Ross!