Forum Discussion
Calculating a Dax function by subsetting by relationships and dates.
I am having a bit of a difficulty with a DAX calculation:
Suppose I am running a business based on sales. In my business, sales people are assigned to different customers. Customers can be switched to another salesperson at any time. I therefore have the following tables:
SalesPeople:
| SalesID | SalesPerson |
| 1001 | Aaron Anderson |
| 1002 | Jane Smith |
Customers:
| CustomerID | Customer |
| 2201 | Barry Baker |
| 2202 | Jessica Snyder |
| 2203 | Michael Lavoie |
SalesPerson-Customer History:
| SalesID | CustomerID | Start | End |
| 1001 | 2201 | 1/1/2023 | 12/31/2023 |
| 1001 | 2202 | 1/1/2023 | 2/14/2023 |
| 1002 | 2202 | 2/15/2023 | 12/31/2023 |
| 1002 | 2203 | 1/1/2023 | 12/31/2023 |
Customer Sales:
| CustomerSalesID | CustomerID | Amount | DateOfPurchase |
| 2201-02072023 | 2201 | $10 | 2/7/2023 |
| 2202-02062023 | 2202 | $10 | 2/6/2023 |
| 2202-02222023 | 2202 | $20 | 2/22/2023 |
| 2203-02182023 | 2203 | $15 | 2/18/2023 |
I want to calculate the number of visits and sales by both customer and sales people as well as the number of customers assigned to each salesperson for the month of February.
Ideally I would want this for customers:
| Customer | Visits | Sales |
| Barry Baker | 1 | $10 |
| Jessica Snyder | 2 | $30 |
| Michael Lavoie | 1 | $15 |
And this for salespeople:
| SalesPerson | Total Guests | Total Visits | Total Sales |
| Aaron Anderson | 2 | 2 | $20 |
| Jane Smtih | 2 | 2 | $35 |
I therefore create the following DAX functions:
Guests = CALCULATE(DISTINCTCOUNT('Customer Sales'[CustomerID]), USERELATIONSHIP('Customer Sales'[CustomerID], Customers[CustomerID]))
Visits = CALCULATE(DISTINCTCOUNT('Customer Sales'[CustomerSalesID]), USERELATIONSHIP('Customer Sales'[CustomerID], Customers[CustomerID]))
Sales = CALCULATE(SUM('Customer Sales'[Amount]), USERELATIONSHIP('Customer Sales'[CustomerID], Customers[CustomerID]))
Total Guests = CALCULATE([Guests], USERELATIONSHIP('Customer Sales'[CustomerID], 'SalesPerson-Customer History'[CustomerID]), USERELATIONSHIP('SalesPeople'[SalesID], 'SalesPerson-Customer History'[SalesID]), Filter(
'Customer Sales',
'Customer Sales'[DateOfPurchase] >= min('SalesPerson-Customer History'[Start]) &&
'Customer Sales'[DateOfPurchase] <= max('SalesPerson-Customer History'[End])
))
Total Visits = CALCULATE([Visits], USERELATIONSHIP('Customer Sales'[CustomerID], 'SalesPerson-Customer History'[CustomerID]), USERELATIONSHIP('SalesPeople'[SalesID], 'SalesPerson-Customer History'[SalesID]), Filter(
'Customer Sales',
'Customer Sales'[DateOfPurchase] >= min('SalesPerson-Customer History'[Start]) &&
'Customer Sales'[DateOfPurchase] <= max('SalesPerson-Customer History'[End])
))
Total Sales = CALCULATE([Sales], USERELATIONSHIP('Customer Sales'[CustomerID], 'SalesPerson-Customer History'[CustomerID]), USERELATIONSHIP('SalesPeople'[SalesID], 'SalesPerson-Customer History'[SalesID]), Filter(
'Customer Sales',
'Customer Sales'[DateOfPurchase] >= min('SalesPerson-Customer History'[Start]) &&
'Customer Sales'[DateOfPurchase] <= max('SalesPerson-Customer History'[End])
))
This works well for the Customers table. However, for the SalesPerson Table I get this:
| SalesPerson | Total Guests | Total Visits | Total Sales |
| Aaron Anderson | 2 | 3 | $40 |
| Jane Smtih | 2 | 3 | $45 |
I believe what is happening is that DAX is taking the overall min of Start and the overall max of End for each Salesperson, when what I really want it for it to take the start and end date for each Salesperson-Customer relationship and for it to filter dates based on that. Is there a way to resolve this?
2 Replies
Hi mmaleta85
You can refer to the following way.
1.Create two columns in history table.
Price = SUMX ( FILTER ( 'Customer Sales', [CustomerID] = EARLIER ( 'SalesPerson-Customer History'[CustomerID] ) && [DateOfPurchase] >= EARLIER ( 'SalesPerson-Customer History'[Start] ) && [DateOfPurchase] < EARLIER ( 'SalesPerson-Customer History'[End] ) ), [Amount] )Count = COUNTROWS ( FILTER ( 'Customer Sales', [CustomerID] = EARLIER ( 'SalesPerson-Customer History'[CustomerID] ) && [DateOfPurchase] >= EARLIER ( 'SalesPerson-Customer History'[Start] ) && [DateOfPurchase] <= EARLIER ( 'SalesPerson-Customer History'[End] ) ) )Then put them to the table visual
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hey there. Thanks for getting back to me. This is helpful. However, I'd prefer this to be a measure so that I could also drill down on dates. For example, if I put a slicer on 'Customer Sales[Date]' for Dates after February 15th, I would want this table:
SalesPerson Total Guests Total Visits Total Sales Jane Smtih 2 3 $45
