Im facing the issue creating an appropiate data model for my 2 report requirements. The 2 CSV files are is like the following
|Sales ID||Customer||Sale Amount||Date|
|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.
Solved! Go to 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
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]))
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.
Proud to be a Datanaut!
@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
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.