Data Modeling and DAX, linking date dimension and other dimesion

[Mkrishna]

Hi Everyone, I hope you are doing well and thank you reading my queries. 

I have three tables

1. Fact Table : It contains customer, customer name, sales , date , brand column. I have extracted month and year from the date column. The table basically shows the visit date of store and which product were there at the time of visit and the sales

2. Customer Table: This table contain the total list of store in month wise with customer number, customer name, sales manager, sales rep and the date. The date column show which month and year  when the list was downloaded. The number of store can change month wise so there is time stamp.

3. Date table.

I am new to PBI and this is the first time I am modeling a table where there is date in dimension table  Customer Table. 

My goal.

I want to execute followint things via this project.

1. When the date is selected in slicer (let say in this example the user select Dec -23), it should filter the Customer table to Dec -23.

2. Now see how many customer present in Dec -23 in the customer table visited the store in last 3 months. i.e do the intercept of  Dec -23 Customer Table's customer with  (Oct, Nov, Dec ) data from Fact Table. The intercept count gives us the total customer for Dec- 23.

 Similary, if the selected date in date slicer is Nov -23 ,  it should filter the Customer table to Nov - 23. Now see how many customer present in Nov -23 in the customer table visited the store in last 3 months. i.e do the intercept of  Nov -23 Customer Table's customer with  (Sep, Oct, Nov ) data from Fact Table. The distinct count of the numbe rof intercept customer gives us the total customer for DecNov-23

3. Now from the list of Dec -23 customer which we found in step 2, see what is the latest visit from the Fact table and see if the latest visit has brand Coca. If yes give 1, else 0. The sum all 1. This give the total customer.

I am able to make a simple data model when there is no date dimension in customer table but now since there is date dimension in customer table, I am unable to model. should it be model as two fact table data model. When I link date table and fact table and similary link fact table and customer table. I am unable to shift the date to backward. i.e when selected month is Dec- 23, I am unable to go to Dec, nov and oct. In my previous model i,e simple one I have use another date dimension and connected it with date dimesion and made the relationship inactive.

Here is the PBIX file https://drive.google.com/file/d/1ICZZfFk_Grrdw-63xuSCNQ0h01LmWVIj/view?usp=sharing

[Daniel29195 · Accepted solution]

@Mkrishna 

option 1 

link date 1 to many to fact 

link customer 1 to many to fact 

link date to customer via an inactive relationship .

now whenver you need to calcualte anything base on the linkage between customer and date,  you should use in your measure : 
calculate ( sum( table_name[col_name] ) ,  userelationship ( customer[date] , date_table[date]) )

option2 : 
now basically, you can create w new date table that links to customer  and the original ddate links to fact table . 

since , the date in customer for the business is different than the date in fact table for the business. 

but now you have 2 dates table, that you can work on . 

now i would recommend using the first approach . 

however sometimes you need to creates 2 date tables. it all depends on your requirements / 

If my response has successfully addressed your issue kindly consider marking it as the accepted solution! This will help others find it quickly. Dont forget to hit that  thumbs up button 🫡👍