Help to foundation setup - Calender problems - want historic sales for week, month, quarter, year.

[jaidee]

I have tried a basic setup, but haw some calendar problem when I want to go back in time

1)

I have made some reference tables in the query editor to get OrdersDay, Orders Week, OrdersMonth, OrdersQuarter, Orders Year. The duplicate tables is sorted via filters on the sales date colum in the query editor for day, week, month and so on. I dont know if this is the best way, because I think it loads a lot of rows every refresh?

But none the less it works fine and I can show reports for every store for current, day, week, month and so on.

2)

But when I want to show sales for all stores or a certain store for last year, a certain month and so on I have trouble. I have tried to make a calendar table and connected it to the sales date row in my Order table, but it dies not work.

But is it not the best way to have a calendar table?

I have done a workaround in the query editor, where I have on the Orders table (all orders) made two duplicate rows of the sales date colums. Then I have converted the one to Year and the other to Month. Both in text  format to get it to work.

Then I can slice on theese two and se store sales each month, but it does not seems to be the right way.

Picture of the relationships:

[MattAllington]

Read my blog about calendar tables and see if that helps. http://exceleratorbi.com.au/power-pivot-calendar-tables/