Home Forums Power Pivot Why do all the examples always reuse the same calendar

This topic contains 3 replies, has 3 voices, and was last updated by  tomallan 3 years, 3 months ago.

Viewing 4 posts - 1 through 4 (of 4 total)
  • Author
    Posts
  • #3698

    PAPutzback
    Participant
    • Started: 26
    • Replies: 28
    • Total: 54

    It does not seem intuitive to me to have a table called Calendar and then have it point to multiple columns, i.e ShipDate, OrderDate, SalesDate… For one, I thought certain functions don’t work on inactive relationships. And two, when you drag a date field or hierarchy to the pivot table, how do you know what date you are referring to. Examples in the book show a Slicer named calendar. What date is it slicing.

    #3699

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2556
    • Total: 2563

    I have had success with a single calendar table pointing to multiple columns in another table.

    The default date is determined by the active relationship, to work with the other relationships requires the use of the USERELATIONSHIP() function.

    #3701

    Avi Singh
    Participant
    • Started: 5
    • Replies: 17
    • Total: 22

    You could use multiple calendars. For each date a seperate one. See also The definitive guide to DAX by Marco Russo & Alberto Ferarri page 162.

    #3703

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2556
    • Total: 2563

    Yes, you can use multiple calendars, and I suppose many people do and benefit, but here is one thing you only get with a single calendar and lose with multiple calendars:

    With field(s) from the calendar table on rows or columns, you can easily compare calendar-driven measures side-by-side in the same pivot.

    And I think that can be a lot to gain or a lot to lose, depends on where you stand.

    Tom

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic.