How to filter visuals correctly when using multiple dates

[HarrisonBi]

Hi,

I would like to know how (if possible) I can filter visuals correctly when I am using multiple dates. I will explain my situation below:

I have a date table and a sales table. These two tables are related by Date[Date] and Sales[InvoiceDate] as I usually report sales based on the invoice date and this has worked fine. 

However, on the sales table there is also another date called CreationDate, I would now like to see all my sales broken down by the creation date. So what I have done is, created an inactive relationship between Date[Date] and Sales[CreationDate] and created a measure 

CALCULATE(Sales[SalesTotal],

USERELATIONSHIP('Date'[Date],Sales[CreationDate]))

This works and I am able to use various columns from the date table (i.e. Financial Year) and see my sales according to the CreationDate - I have this displayed in a matrix.

However the problem I am facing is - if I click a month in the matrix, all the other visuals on the page are filtering the results based on the InvoiceDate NOT the CreationDate. I believe this is because the active relationship between the two tables is based on InvoiceDate.

How can I make the other visuals filter based on CreationDate when clicking a month in my matrix ?

Thanks

[Ashish_Mathur]

Hi,

I think you will have to rewrite all the measures with the USERELATIONSHIP() function.

[amitchandak]

@HarrisonBi , When you filter months from the date table. Visual using USERELATIONSHIP('Date'[Date],Sales[CreationDate]) will filter on Creation date.