Fetching a related max date for comparison

[josteinross]

Hi! I tried searching around but I couldn't find a good answer to this problem.

I have two F tables, Product and Activity. What I want to do is count up all the customers who have a bought a product and was contacted during the 30 days prior to purchasing. My code looks like this:

Contacted customers with product =

VAR END_DATE =

MAX('F Product'[Date])

VAR START_DATE =

END_DATE - 30

RETURN

CALCULATE(

DISTINCTCOUNT('F Product'[Customer])

,'F Product'[Customer] IN (VALUES('F Activity'[Customer]))

,'F Activity'[Date] > START_DATE

,'F Activity'[Date] < END_DATE

However, this code gives me the absolute largest date value from Product and uses that as a comparison. What I need is the largest date value per customer when that particular customer is evaluated. Any help would be greatly appreciated.

Thanks!

[Hosseinbrz]

You need to use Row context in Dax, when u have Max(date) out of calculate loop it doesnt work on Row context . take Max (date) inside the calculate and replace it with END_DATE and see if it works .

[josteinross]

Hi, thank you for response. This did not work as expected, and I believe the reason is that I do not have some form of relationship between the two F tables. Thus, the formula doesn't fetch the correct max date value from the activity table.

[Hosseinbrz]

Hei.

I have tried with DAX Studio to have better overview . probabely your customer table have duplicate cusotmer ID as a cusotmer may buy lots of products , so the first step is to GROUP BY by your customer and MAX date of purchasing product. Now you have a table of each customer with their latest purchase date. Just to visually see what happens , you can use ADDCOLUMN to your new table , this column is condition column , where you take each customer_ID and Maxdate and filter the activity table if there has been an calling on last 30 days for that cutomer and count the rows .

Hope it works for you as well.

Customer 

ID	Date
1	14/03/2022
2	27/08/2022
3	10/03/2022
1	27/06/2022
1	14/10/2022
2	16/10/2022

Activity:

ID	Date
1	22/02/2022
2	28/08/2022
3	18/02/2022
1	28/06/2022
1	15/10/2022
2	17/10/2022

[Hosseinbrz]

DEFINE
table _tbl= GROUPBY(customer,customer[ID],"maxdate",maxx(CURRENTGROUP(),customer[Date]))
EVALUATE ADDCOLUMNS(_tbl,"condition",COUNTROWS(FILTER(Activity,Activity[ID]==[customer_ID] && DATEDIFF([maxdate],Activity[Date],DAY)<=30 && DATEDIFF([maxdate],Activity[Date],DAY)>=0)))

[Mahesh0016]

Contacted customers with product =

VAR END_DATE =

MAX('F Product'[Date])

VAR START_DATE =

END_DATE - 30

RETURN

CALCULATE(

DISTINCTCOUNT('F Product'[Customer])

,'F Product'[Customer] IN (VALUES('F Activity'[Customer]))

,'F Activity'[Date] > START_DATE && 'F Activity'[Date] < END_DATE

[josteinross]

Hi! I think you misunderstood me, this provides the same answer. What I need is for START_DATE and END_DATE to be the max date value from F Product for the specific customer being evaluated in the final expression.

[Mahesh0016]

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[josteinross]

Unfortunately I can't share any data due to company policy. However I can give you some sample tables to show you what I mean:

PRODUCT TABLE

Customer	Date purchased
1	January 1st
2	January 10th
3	January 17th

ACTIVITY TABLE

Customer	Date contacted
1	January 2nd
2	January 7th
3	January 15th

Here, the contacted date for customer 2 & 3 comes before the purchase date, meaning I want to count them so expected output should be 2. However, currently the model does not take the max purchase date per customer and compares it to the activity table, but rather takes the max purchase date in the product table overall, being January 17th. This makes the output 3 instead of 2. 
Note: Both of these are fact tables in my model so they can't have a direct, active relationship.