• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Is there any function like calender that create a time table

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.
 
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...
 
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/
 

Attachments

  • Time data table.csv
    928.2 KB · Views: 1
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.
 
Back
Top