Calculating a Dax function by subsetting by relationships and dates.

[mmaleta85]

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?

[Anonymous]

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.

[mmaleta85]

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