Transform Date/Time to Date for Calendar

[oparra]

Hello, first post.

I want to create a calendar table that includes specific date/time, because I download the reports that includes the exact time for a transaction.

So I need to create a Calendar table to create a relation as I have multiple tables that need to be filtered from this one.

The left one is the one from the report, and when I try to filter data from the calendar table it shows as a blank.

So far I tried to duplicate the TimeStamp Column and ROUND the data, but the column is not recognized as a Date even if it has the correct format.

Hope you can get what I want and can help me.

Thanks in advance.

Osiel.

[DataNinja777]

Hi @oparra 

When you need to also include time in your dimension table, the best practice is to separate out the date table and the time table, instead of having one big date/time calendar table with near infinite combination (cardinality) of date and time unique values.  Separating out date and time will ensure that the cardinality of the dimension table is maintained at a level where there is minimum performance issues, and you can have a time table of 24 hours.

Best regards,    

[oparra · Accepted solution]

For now I did a new colum with:

Date = Append1[TimeStamp].[Date]

The column excluded all the time and only shows the date, and the relation with the CALENDARAUTO table seems to work properly for now.

I'll keep checking.

Thanks