cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
webportal
Impactful Individual
Impactful Individual

How to create a calendar table with second granularity?

I've built a calendar table using Power Query for the whole year of 2022 using second granularity.

 

Basically, by "cross joining" a calendar table with day granularity (365 rows) with a time table (24*60*60 rows).

 

The table contains 31.536.000 rows, all distinct values for column DateTime.

 

Now, after relating this table with a transacional table, the visuals do not show any values.

 

Here's a sample of the transactional table:

 

webportal_0-1644001819903.png

Although all these timestamps are present in the calendar table, I get an empty matrix when trying to visualize the results.

 

1 ACCEPTED SOLUTION
elofstrom
Resolver II
Resolver II

In the Transaction table does the column you're creating the relationship on have timestamps as well?  If not then you won't get any matches because the Dates aren't lining up to the Timestamps.

Generally speaking in data modeling we keep our Dates and Times separate.  First, it cuts down on data volumes (only 86400 rows in the Time table and 365/366 rows per year in the Date table). 
I would try that - separate your Date and Time dimension tables and separate your Dates and Times in the transaction table.  Still gives you the capability to drill-down to the second level without super-sizing your model.

View solution in original post

4 REPLIES 4
webportal
Impactful Individual
Impactful Individual

@elofstrom 

I've managed to relate the DateTime table with the transactional table by converting the timestamp into integers (as the difference in seconds to the 1st datetime). This worked perfectly.

 

I also separated the simple Date and Time into two different tables with the great advantage - as you well refer - to decrease the size of the model.

 

Thanks for helping!

webportal
Impactful Individual
Impactful Individual

@elofstrom 

Yes, both tables have timestamps, that's why I don't understand why the relationship doesn't work.

 

Thanks for the tip, but what if you want to have continuous visuals? Eg: a line chart for a certain week where you may scroll horizontally to the second?

My guess is that the matching on the timestamps simply isn't lining up (although it should - datetimes are stored as numbers under-the-sheets so I'm surprised it's not working).  I'd try the "resolve-it-to-yyyymmddhhmmss-format-and-join-on-that" approach.

As far as creating visualizations (if you have separate date and time dimensions) you could simply stack your date fields on the axis shelf and then stack your time fields underneath them and the drill-down/expand-down should work with no issues.

elofstrom
Resolver II
Resolver II

In the Transaction table does the column you're creating the relationship on have timestamps as well?  If not then you won't get any matches because the Dates aren't lining up to the Timestamps.

Generally speaking in data modeling we keep our Dates and Times separate.  First, it cuts down on data volumes (only 86400 rows in the Time table and 365/366 rows per year in the Date table). 
I would try that - separate your Date and Time dimension tables and separate your Dates and Times in the transaction table.  Still gives you the capability to drill-down to the second level without super-sizing your model.

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors