Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Date Dimension, Filter based on matched dates in multiple dimension

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

 

Need Help

4 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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?