I feel that a date table is never appropriate in my work, am I not understanding something?

[olivierhbh]

Hello all,

I read this post (The Ultimate Date Table - PowerPivotPro) and a few others, and I understand that in the mentioned cases it's quite useful.

Let's consider 2 tables Trades and Invoices, related by the trade id (1 trade has several invoices).

In my Trades table I can have the trade date, shipment date, setup_date and in my Invoices table I can have the setup date and the due date.

How should I include a date table in this model? How could I connect all my date fields to this date table?

I feel that I'm missing something there, could you help?

Many thanks,
Olivier.

(Example Workbook if needed : https://www.dropbox.com/s/962wo6xmda1ma7q/example_dates.xlsx?dl=0)

 

[Sinon]

Seems to me like you created a relationship between the Trades and Invoices tables. If you want to use the Dates table, you would have to create relationship between the Dates table and one of the other 2. However, i can't see that you actually need the Dates table so what exactly are you trying to achieve?

If you are simply trying to understand power pivots better, the best way is to have an actual example to work with. Personally, I use power pivots at work because we have 2 different ways of logging income and expenses. So I use power pivots to create a relationship between these 2 based on the broker name (I work in insurance). I have a table with insurance premiums by broker and another with insurance claims by broker and i created a relationship between the 2 broker columns to be able to get both the premium and the claims amounts in one go, without looking at both tables.
Does that help?

 

[olivierhbh]

Hello Sinon,

Yes I created this relationship because when working with invoices I need to slice my measures according to trades information, then here my cut off date could be any of the dates I have in my base, and this changes according to the analysis I'm doing.

I know how relational modeling works, just that I don't know how to integrate a "date table" to my model, so I can be able to use the name of the months for all of my date fields for example.

My question is not about how to use Power Pivot relations in general, but how to integrate a specific calendar table like the one in the link mentioned in my first post, so that I can use it to compute stuff with my date fields.

Hope it's clear.

Thanks,
Olivier.