Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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 prod...
Need Help
Mahesh0016's avatar
Mahesh0016
Icon for Super User rankSuper User
3 years ago
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
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
    Mahesh0016
    Icon for Super User rankSuper User
    3 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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

      CustomerDate purchased
      1January 1st
      2January 10th
      3January 17th

       

      ACTIVITY TABLE

      CustomerDate contacted
      1January 2nd
      2January 7th
      3January 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.