Date Dimension, Filter based on matched dates in multiple dimension

[Anonymous]

Hi Guys,

We have below 4 tables

Dim.Date

    Date  -Column

Dim.Shipment

    ShipmentID  -Column

    ShipmentDate  -Column

    ShipmentTime  -Column

Dim.Order

   OrderID-Column

   OrderDate-Column

   OrderTime-Column

Dim.Time

   Time  -Column

We creatd report based on the tables mentioned above; Now we want to show date as filter; as my date dimesion have all the dates raning from 2000-21000; when we select the date from Dim.Date; it shows too many dates; We want to show only dates which match Order and Shipment and should be able to work for both; 

Issue 1: Dim Date as filter show all dates

Issue 2: Dim ShipmentDate as filter show only Shipment dates and visualize pertaining to order does not work

Issue 3: Dim OrderDate as filter show only ORderdates and visualize pertaining to Shipment does not work

Regards,

Navin

[Ashish_Mathur]

Hi,

Why do you have dates going till year 2100?  Ideally the Date table should only have dates till the farthese date in your datasets.  Assuming the farthese date will be found in the OrderDate column, try this Table formula

Calendar = calendar(min(Shipment[ShipmentDate]),max(Order[OrderDate]))

Hope this helps.

[Anonymous]

Date dimension not only used for shipment dates and order dates it used by other dimension too, some of the dimension have furture dates requirement; what if min and max dates dont match with each other?

[Ashish_Mathur]

Hi,

Please explore the Calendarauto() function.

[Anonymous]

not working