Datetables for datetime records

[Anonymous]

Hi, I'm new to datetables and trying to get this right.

So far every tutorial i've seen works with data prepared at the date level. (yyyy/mm/dd) 

But my issue is that I have several columns across several tables ALL in date/time format. (yyyy/mm/dd hh/mm/ss)

When I create a relationship between my datetable and my datetime columns, it only shows records that exist EXACTLY at 12:00:00AM. I was expecting PowerBI to handle timestamps more intellegently than this... and I hope I'm simply missing something.

I've tried both a date table with yyyy/mm/dd 12:00:00 set to date/time and also yyyy/mm/dd set to date

Question1) To link datetimes to a date table, do I need to separate the timestamp from the date for every single table/column in my source data?

Question2) If I attempt to do a timetable too (24hour increments for example) does my source data need the timestamp truncated to the hour?

[amitchandak]

You can split datetime into two parts date and time. create and change datatype to date and time

date = format(datetime,"dd/mm/yyyy")

time= format(datetime,"HH:MM:SS")

The date you can join with the date dimension. And the time you can join with some time buckets if needed

You do need to anything to source data, just have additional columns here to deal with date and time

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[Anonymous]

@amitchandak Thanks. To have hourly time buckets, 

time= format(datetime,"HH:MM:SS") wouldn't work right? I would need to truncate the minutes and seconds, correct?

[amitchandak]

Better to have HH only, In that case, you do not put a logic to join

[az38]

Hi @Anonymous 

in your table with datetime create a calculated column Date then create relationships with datetable:

DateColumn = DATE (YEAR([datetimeColumn]), MONTH([datetimeColumn]), DAY([datetimeColumn]))

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

Ok, so its as I feared then... that is a lot of extra columns because I have a lot of datetime columns. 

[az38]

@Anonymous 

anyway, you will be able to create relationships only by ONE column.

do not hesitate to give a kudo to useful posts and mark solutions as solution