What should be my Date range in the dCalendar table?

mrxlsx

Board Regular
Joined
Dec 15, 2012
Messages
95
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
 

scottsen

Well-known Member
Joined
Mar 16, 2014
Messages
1,263
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.
 

mrxlsx

Board Regular
Joined
Dec 15, 2012
Messages
95
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.
 

scottsen

Well-known Member
Joined
Mar 16, 2014
Messages
1,263
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 :)
 

mrxlsx

Board Regular
Joined
Dec 15, 2012
Messages
95
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:

mrxlsx

Board Regular
Joined
Dec 15, 2012
Messages
95
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.
 

TimRodman

Board Regular
Joined
Jan 21, 2015
Messages
94
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.
 

mrxlsx

Board Regular
Joined
Dec 15, 2012
Messages
95
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.
 

TimRodman

Board Regular
Joined
Jan 21, 2015
Messages
94
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.
 

Forum statistics

Threads
1,085,915
Messages
5,386,754
Members
402,019
Latest member
JLuby

Some videos you may like

This Week's Hot Topics

Top