Forum Discussion
Fetching a related max date for comparison
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:
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.
8 Replies
- Mahesh0016
Super User
Contacted customers with product =VAR END_DATE =MAX('F Product'[Date])VAR START_DATE =END_DATE - 30RETURNCALCULATE(DISTINCTCOUNT('F Product'[Customer]),'F Product'[Customer] IN (VALUES('F Activity'[Customer])),'F Activity'[Date] > START_DATE && 'F Activity'[Date] < END_DATE- Mahesh0016
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
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 .
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
