Modelling and reporting with multiple date dimensions. RRS feed

  • Question

  • Our business model involves a lot of dates and the business owners frequently want reports based on each of these different dates. For example in any given order there are as follows:

    - Order created date
    - Client due date

    - Order first payment date (an order can have multiple payments)
    - Order fully paid date
    - Date assigned to vendor
    - Vendor return date
    - Date delivered to client

    On top of that we have other areas of the business, the data from which ties into the above. Here we have more dates e.g.

    - Date vendor recruited
    - Date vendor reviewed

    At any given point the manager may want a report based on any of these dates. For example;

    - Product type by order creation date (fiscal year / month)
    - Product type by first payment date  (fiscal year / month)
    - Product type by client due date (fiscal year / month)

    and so forth. I have been asked to create a report using all of the above on at least one occasion, many of them far more frequently. 

    At the moment I have created a standard date table and then duplicated that for each type of date that I need however this is becoming excruciating to work with as I have approximately 10-12 date tables in my data model. 

    Is there a better way of doing this now, in Excel 2013? If not, is there an improvement in 2016 that may make life easier? 

    Wednesday, June 17, 2015 7:59 PM


All replies

  • Have a look a the "Role Playing Dimensions":



    • Marked as answer by Maracles Friday, June 19, 2015 6:26 AM
    Thursday, June 18, 2015 4:50 AM
  • Thanks for this - I have seen this suggestion before but without the video and I didn't think it would work, having watched the video now though I can see how it might (just means I have to rewrite all my metrics :-( !).

    Friday, June 19, 2015 6:26 AM
  • Yes, that's the downside.

    Quickest way would be to unpivot your different date columns in Power Query first. Then you'd just have one date column to be linked to your DimDate & create just one measure. The name of the date columns will sit in a new Dimension/Attribute column that you drag into rows or columns of your Pivot-reports.


    Friday, June 19, 2015 7:27 AM
  • Thank you for the advice.

    How do you mean unpivot the date columns? At the moment my data is being bought in via an SQL query directly into PowerPivot.

    I think understanding that and your above advice may help with my two remaining questions now that I am trying this method (which is initially working).

    - How do you organise naming. Previous each date table had specific naming e.g. calDateOrdered, calFirstPay. Using "Role Playing Dimensions" it seems that my date columns from various tables are all linked to one date table which I am calling 'calGeneric'. Do I just have to use very specific naming for each metric e.g. orderCountDateOrdered, orderCountFirstPayDate? 

    - linked to above, does this now mean that pretty much all my date related metrics will have to include the USERELATIONSHIP() function?

    Friday, June 19, 2015 8:21 AM
    1. Yes, your measures should now carry the hint to the date type
    2. Only the measures that connected by the inactive relationships will have to use USERRELATIONSHIP, the ones on the main active one can stay untouched.

    These are some of the reasons, why this method isn’t so popular.

    My PQ-suggestion was meant as an alternative to the role-playing-dimension method. It means restructuring your data model in order to make your DAX-live easier. But if PQ is new to you, this would probably not be the quickest way for you at the moment J

    If you have many measures to rewrite, maybe DAX Studio could help you: It shows all your existing measures in an excel-list. There you could do some Excel-formula magic to “translate” all your existing measures into the new strings which you then just copy & paste back into your model.



    Friday, June 19, 2015 9:03 AM
  • Ah I see, ok thanks very much for your suggestions - I'm downloading DAX studio now, it seems like it could make my life much easier.
    Friday, June 19, 2015 9:29 AM