Is there any function like calender that create a time table

[Kumar Shanmugam]

I have a transaction data containing column transaction date and time.

I create a date table using the calendar function

DATETABLE = CALENDAR(min('2018'[COLLECTION DTM]),max('2018'[COLLECTION DTM]))

Similarly, I want to create a time data table capturing minimum of colleection DTM and maximum of collection DTM.

 

[Hui]

Kumar

You can easily create a Calendar effect if you have a Start date and End Date or Duration.

I'd recommend posting a sample file of what you have and what you want as a result...

 

[GraH - Guido]

Kumar, take a peak over at the powerBI forum: https://community.powerbi.com/t5/De...calendar-table-with-date-and-time/td-p/241728

Though on same forum, a link to best practices explain that it is best to use a separate table for time dimension.
https://www.sqlbi.com/articles/data-import-best-practices-in-power-bi/

 

[Kumar Shanmugam]

Hi Yes you are right.

I want to create a time data table which will range from 00:00:00 to 23:59:59.
The reason behind time data table creation: I read an article of data import best practices - Split date and time

https://www.sqlbi.com/articles/data-import-best-practices-in-power-bi/

However, i created manually in excel file please find the file attached. This contains time range from 00:00:00 to 23:59:59. So its applicable for any date period.

My source file contains collection date and time. So date table is linked with collection date.

I will link the time data table with collection date.

I posted query as i wanted to know is there any function like calender that generates a time range from 00:00:00 to 23:59:59

Please find the file attached.

Kumar, take a peak over at the powerBI forum: https://community.powerbi.com/t5/De...calendar-table-with-date-and-time/td-p/241728

Though on same forum, a link to best practices explain that it is best to use a separate table for time dimension.
https://www.sqlbi.com/articles/data-import-best-practices-in-power-bi/

 

[Kumar Shanmugam]

Hi Please find attached the sample data

 

[Chihiro]

I don't recommend Time dimension table, unless you are looking at specific range of data (say less than 1 week for hourly, 48 hours for 15 min interval or less).

You can see how it's done at PowerQuery stage in link below.
https://chandoo.org/forum/threads/power-pivot-time-aggregation-charting.33730/

While it's specifically for Hourly range, you could easily adopt it to different time grain.

And no, there is no function equivalent to Calendar, that performs this operation on Time in DAX.