Forum Discussion
Multiple dates tables
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/
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.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.
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.
