Many to many relationship - Type 2 data from CSV

[NipponSahore]

Spoiler

Hi All, 

Im facing the issue creating an appropiate data model for my 2 report requirements. The 2 CSV files are is like the following 

format.

Sales
Sales ID	Customer	Sale Amount	Date
1	1	10	3/1/18
2	1	120	1/1/18
3	2	11	3/1/18
4	3	14	3/1/18
5	3	15	2/1/18

Field Details
Customer	Employee	Valid From	Valid to	Valid record
1	John Doe 1	2/16/18	12/31/99	TRUE
2	John Doe 2	2/16/18	12/31/99	TRUE
3	John Doe 3	2/16/18	12/31/99	TRUE
1	John Doe 2	12/31/17	2/15/18	FALSE
3	John Doe 2	12/31/17	2/15/18	FALSE
4	John Doe 1	2/16/18	12/31/99	TRUE
5	John Doe 3	2/16/18	12/31/99	TRUE
6	John Doe 2	2/16/18	12/31/99	TRUE

I need to create 2 visuals which allows me to following: 

1. Total Sale amount made by each Employee (For Example: In case of John Doe 1 this is 10 and John Doe 2 is 146)

2. matrix to show past and future sales including new opportunities for sale per Employee. (For Example: for John Doe 1 -  This is should show sales with Sales ID 1,2 as well as opportunity for 4)

Any Help would be greatly appreciated.

[Ashish_Mathur]

Hi,

You should always explain the result.  Why is the asnwer against John Doe 2 131?

[NipponSahore]

@Ashish_Mathur the total for John Doe 2 should be 131 because he has made 2 sales in total. One for Customer 1 for 120 and one for Customer 2 for 11 which totals to 131

[Ashish_Mathur]

Hi,

Why should you not consider Customer 3 for John Doe 2?  There are 2 dates coresponding to customer 3 in Table 1 - I Feb 2018 and 1 March 2018.  1 Feb 2018 falls in the date range specified in Table 2 for Customer 3.

[NipponSahore]

Sorry, My Bad. It includes Customer 3

the total sale  will be for John Doe 2 146

[Ashish_Mathur]

Hi,

You may refer to my solution here.

Hope this helps.

[NipponSahore · Accepted solution]

Also, Thank you to the community,

I was able to find an answer somewhere else, but anyone is still looking for the answer here,

 I created a calculated column "Employee_Sold" and used 2 variables to join and find the answer

Employee_Sold =>

var thisdate = SALES[date] var thisterritory = SALES[customer] return CALCULATE(FIRSTNONBLANK(FieldDetails[Employee],0),FILTER(all(FieldDetails),FieldDetails[Valid from] < thisdate && thisdate <FieldDetails[Valid to] && thisterritory = FieldDetails[customer]))

[Greg_Deckler]

Why is 4 an opportunity?

[NipponSahore]

Customer 4 is an opportunity, as John Doe 1 not made a sale for it. 

[Greg_Deckler]

OK, great, but what in the data says that? I don't see it.

[NipponSahore]

Hi Greg,

As this record is present in field details table but a sales entry is missing.

my major concern is how can relate the two data sources which have many to many relationship and solve the issue of issue of where I can use one slicer and be ae to select all past and present sales made for one customer by multiple employees

[Greg_Deckler]

To solve many-to-many, create a bridge table of unique ID's and relate that table to both of your other tables. Use your bridge table in your slicer.