Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: What should be my Date range in the dCalendar table?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular mrxlsx's Avatar
    Join Date
    Dec 2012
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Cool What should be my Date range in the dCalendar table?

    Hello all,

    This is a cross-post from Chandoo.Org where it attracted almost nobody.

    This is a PowerPivot question.

    I have Fact table and couple of dim tables. In my Fact table I have my sales DATE range from 01-Jan-2008 TO 31-Dec-2014 in the OrderDate column.

    Where as in my ShipDate column this range starts from 01-Jan-2008 TO anywhere 80+/- days +31-Dec-2014, where as another dim Table's Date column starts from 30+/-days of 01-Jan-2008 TO 180+/- days of 31-Dec-2014.

    What should be my dCalendar table's date range start from and end till??

    Thanks

  2. #2
    Board Regular scottsen's Avatar
    Join Date
    Mar 2014
    Location
    Seattle, WA
    Posts
    1,263
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: What should be my Date range in the dCalendar table?

    I generally think of it as "the range of dates I care about" -- meaning, that I am likely to put on rows of my pivot, or columns of my chart over time, or whatever. Safe would be "the min date you see, through the max date you see" in your fact table.
    scott at tinylizard.com : Power Pivot Consultant, Trainer and Mercenary
    See my Power Pivot blog at http://tinylizard.com/blog

  3. #3
    Board Regular mrxlsx's Avatar
    Join Date
    Dec 2012
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: What should be my Date range in the dCalendar table?

    Hi scottsen,

    Thanks for replying. But in general, OrderDate and ShipDate may not be the same always. If I want to analyze on ShipDate (which is "n" days +/- of OrderDate), you need to have them in your source table, right? So you have to consider the whole date range that you can see in your source tables. Otherwise whenever you need to analyze, you will miss the same if you don't have.

    Anyways I got the point. Thanks a ton, man.

  4. #4
    Board Regular scottsen's Avatar
    Join Date
    Mar 2014
    Location
    Seattle, WA
    Posts
    1,263
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: What should be my Date range in the dCalendar table?

    Right I am saying... your date table should cover from the smallest of ANY date, through the max of ANY date. But... really, it's more based on what you want to report on. Maybe your first sale was Apr 1, but you want your report to start on Jan 1... you would want Jan 1 in your calendar table. Or, maybe you have 10 years of sales, but you know you only care about the most recent 3 years... in which case, your calendar table only needs 3 years of data. "it depends" -- the answer of every consultant, always and everywhere
    scott at tinylizard.com : Power Pivot Consultant, Trainer and Mercenary
    See my Power Pivot blog at http://tinylizard.com/blog

  5. #5
    Board Regular mrxlsx's Avatar
    Join Date
    Dec 2012
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: What should be my Date range in the dCalendar table?

    Scott,

    Thank you for the reply ,

    One more question. Can I have formulas in my Fact and dim tables between table to table? or I shall have only pure values?

    mrxlsx
    Last edited by mrxlsx; May 31st, 2015 at 11:12 PM. Reason: change in phrasing

  6. #6
    Board Regular scottsen's Avatar
    Join Date
    Mar 2014
    Location
    Seattle, WA
    Posts
    1,263
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: What should be my Date range in the dCalendar table?

    Once your data is in Power Pivot, you are going to have two choices for "formulas". See Calculated Fields vs Calculated Columns | Tiny Lizard

    And those formulas can certainly span across tables, though, I'm not 100% sure I cam getting your question.
    scott at tinylizard.com : Power Pivot Consultant, Trainer and Mercenary
    See my Power Pivot blog at http://tinylizard.com/blog

  7. #7
    Board Regular mrxlsx's Avatar
    Join Date
    Dec 2012
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: What should be my Date range in the dCalendar table?

    Scottsen,

    I am trying to know that whether we can have formulas in the Source tables (When I say source tables, be it CalendarTable, FactSales, Customers, ProdCategories, SalesmanKey etc) which we import into PowerPivot.

    See this scenario:

    If a particular customer wants to change his Mode of shipping from Airways to Railways or he wants to change one of his Products ordered, we change in our source tables because we cannot change anything in the PowerPivot once we pull the data into the same, right?

    So if we have formulas in our source tables, we change some thing in a table that reflects in other table say, Freight for Airways is 9%, Railways 4%, Roadways is 6%.

    Can I use this flexibility in my data or not? The reason why I am asking this is I doubt that there are formulas already in the source tables, Will they be in conflict with the formulas in the PowerPivot? Will PowerPivot allow us that flexibility of having formulas in the source tables or they should be pure values for the analysis purpose?

    I think your answer may differ now.

  8. #8
    Board Regular TimRodman's Avatar
    Join Date
    Jan 2015
    Location
    Columbus, OH
    Posts
    94
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: What should be my Date range in the dCalendar table?

    I think you might want to run your data through Power Query when bringing it into Power Pivot if you want to add a lot of formulas. Power Query allows you to add some pretty powerful calculations.

  9. #9
    Board Regular mrxlsx's Avatar
    Join Date
    Dec 2012
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: What should be my Date range in the dCalendar table?

    Hi Tim,

    Thanks for the news, I use Power Pivot with Office 2010. But Power Query is available ONLY with Office 2013, right?

    I need to check this what level of functionality it offers in terms of using formulas.

  10. #10
    Board Regular TimRodman's Avatar
    Join Date
    Jan 2015
    Location
    Columbus, OH
    Posts
    94
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: What should be my Date range in the dCalendar table?

    I used Power Query when I was on 2010 (just Google for the download link), but there was a trick to get it to feed into Power Pivot that someone showed me (it might have been Scott). I'll see if I can find it.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •