Date Differences between tables with many:many relationships

[jesspiorier]

Hello,

I need some help with relationship modeling and calculations regarding finding the date difference between events and purchases by customers.  The goal is a conversion distance rate for when a customer attended an event and when they made a subsequent purchase.

'Customer Data'  has a single entry for each [INDIVIDUAL_ID]

'Events' has many entries for each [INDIVIDUAL_ID]

'A-Purchase' can have many entries for each [INDIVIDUAL_ID] (or none)

'B-Purchase' can have many entries for each [INDIVIDUAL] (or none)

I'm looking to find DATEDIFF from an event ('Events' [LEAD_DATE]) to  purchases from either/both purchase tables. I tried creating a table of just distinct Individual IDs to act as a secondary bridge, but my "related" measures gave me errors.  I tried using a DATE table, but I couldn't make the relationship active because of cardinality issues.  I'm hoping someone smarter than myself can help me figure this out.  Thanks in advance.  -Jess

Data Model

[MFelix]

Hi @jesspiorier ,

Although youhave place some information in your post by the image you present and without any data is difficult to give you an answer.

Can you please share a sample file and expected result? If the information is sensitive, difficult to make a mockup or you don't want to share it here you can do it trough private message.

Regards,

MFelix

[jesspiorier]

Hi @MFelix .

Here is a super-simplified view of what I'm trying to accomplish.

Essentially, the number of days between participating in an event/campaign and how long it took them to make a puchase (two different types of purchases, A or B).  

All people will have particiapted in at least one Event, will some will have participated in many.

Some people will have made a purchase from A or B (or both) multiple times, and some people will not have purchased anything at all.

I have a customer table with a single, unique [INDIVIDUAL_ID] for each person. I also have a date table.  I need a measure or calculated column or something to figure out the DATEDIFF between [LEAD_DATE] to [PURCHASE DATE A] (and/or B).  Does that make sense? Is that possible and I'm just over (or under) thinking it?  😆

[MFelix]

Hi @jesspiorier ,

Try the following two measures:

To Purchase A =
DATEDIFF (
    SELECTEDVALUE ( 'A-PURCHASE'[PURCHASE_DATE] );
    SELECTEDVALUE ( EVENT[LEAD_DATE] );
    DAY
)

To Purchase B =
DATEDIFF (
    SELECTEDVALUE ( 'B-PURCHASE'[PURCHASE_DATE] );
    SELECTEDVALUE ( EVENT[LEAD_DATE] );
    DAY
)

Check the PBIX file attach.

Regards,

MFelix