Multiple dates tables

[team_steve]

Hello,

This is probably a silly question but I cannot find a definiative answer so here goes.

I have an orders table containing a Date Entered field, and a Date Invoiced field (also Date Required, Date Promised and Date Despatched). I also have a Dates table/dimension which I've created to allow from a few date calculations.

I have created a relationship between Date Entered and the Dates table and I've created some nice reports showing values of orders by the month they were entered, YTD, comparisons etc.

Now I want to create some reports based on the Date Invoiced. How should this be done? Should I create another Dates table and link to this? Perhaps I should create a whole new data model for invoicing.

Both seem valid but which ever I select there appears to be an element of duplication in the work and data imported. This usually means I'm doing something wrong. I'm sure others most run into this problem often - is there a typical way around this type of problem?

Regards
Steve

[vanessafvg]

I dont think you doing anything wrong, in the tabular model you do need to bring the date table in for each date as far as i know, if its an imported model you could create a calculated table on the the date table instead of physically bringing it in twice.  If its direct query just bring it in twice and rename appropriately. i.e invoice date , sales date etc.

If you wanted to see calculated figures side by side you could just use also switch relationships between your foreign date keys - this article goes into more detail

http://exceleratorbi.com.au/multiple-relationships-between-tables-in-dax/

[team_steve]

Thanks for your response. It wouldn't be difficult to bring in the dates table twice and it is rather small compared to the orders table.
If I was to do this I'd be a bit concerned that the users might get confused as to which date dimension to use if they are designing their own reports. Realistically though, I create almost all reports anyway and they use them. With a good naming on convension I think this problem can be overcome for the users who are a bit more adventurous.

The other idea about selecting which relationship to use is a bit beyond my DAX skills at the moment, which are very basic.

Sounds like loading the dates table in 2 times (or perhaps more) is a valid strategy - so I'm happy with this.
Thanks again.

[vanessafvg]

out of interest what is the purpose of bringing the date dimension in is it for a specific calendar or is there something you need it for?  on date fields power bi automatically creates a invisible date table which allows for generic date functionality i.e. it provides a hierarchy etc.  If you aren't looking for any specific functionality except the use of a typical  hierarchy i would say as long as your date type is date, you should automatically get some date functionality  - when you drag the date on to your object you should be able to switch it between the date value and the hierarchy values.

[team_steve]

I've not really used the built in date, though I think I've the heirarchy kick in when I display a date field on a table visualisation for example. The Dates dimension I bring in breaks down the date into day, month year as you would expect. Also day of week, week number, first date of the week and month, last date of month. Indications of whether a date should be included in YTD, MTD and WTD calculations. Rolling 3, 6 and 12 month periods.
I think the same calculations can probably be done with DAX but I'm not sure what the best method is.

[v-huizhn-msft]

Hi @team_steve,

Based on your description, I am unable to reproduce your scenario, could you please share your .pbix file and list the expected result clearly, so I can analysis furtherly. Thanks a lot.

Best Regards,
Angelia