Forum Discussion
Datetables for datetime records
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?
6 Replies
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
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
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 Linkedinamitchandak 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?
Better to have HH only, In that case, you do not put a logic to join
