Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
XaviOV
Helper V
Helper V

Create table with date and time

Hi,

I need to create a date table with all the hours that a day has and that appear every hour, that is, from 00.00: 00 to 23:00:00, a date with every hour.

The table should start from 2016.

Cheers,

Xavi

2 ACCEPTED SOLUTIONS
V-lianl-msft
Community Support
Community Support

Hi @XaviOV ,

 

You can also try to enter a time table from 0:00:00-23:00:00,and then use the Crossjoin function.

Table 2 = 
ADDCOLUMNS (
    CROSSJOIN (
        CALENDAR ( DATE ( 2016, 1, 1 ), DATE ( 2020, 12, 31 ) ),
       'Table (2)'  
    ),
    "DateTime", [Date]+[Time]
)

Sample .pbix 

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Hi @XaviOV ,

 

Replace 2020 with year (today())

Table 2 = 
ADDCOLUMNS (
    CROSSJOIN (
        CALENDAR ( DATE ( 2016, 1, 1 ), DATE ( YEAR(TODAY()), 12, 31 ) ),
       'Table (2)'  
    ),
    "DateTime", [Date]+[Time]
)

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
V-lianl-msft
Community Support
Community Support

Hi @XaviOV ,

 

You can also try to enter a time table from 0:00:00-23:00:00,and then use the Crossjoin function.

Table 2 = 
ADDCOLUMNS (
    CROSSJOIN (
        CALENDAR ( DATE ( 2016, 1, 1 ), DATE ( 2020, 12, 31 ) ),
       'Table (2)'  
    ),
    "DateTime", [Date]+[Time]
)

Sample .pbix 

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @V-lianl-msft Thanks for the solution, I have a question,

I see that in the formula the end of the formula is until the end of this year, but in the next year this formula will only appear until the year 2020?

What is the way of not having to change the formula every year?

Regards,

Xavi

Hi @XaviOV ,

 

Replace 2020 with year (today())

Table 2 = 
ADDCOLUMNS (
    CROSSJOIN (
        CALENDAR ( DATE ( 2016, 1, 1 ), DATE ( YEAR(TODAY()), 12, 31 ) ),
       'Table (2)'  
    ),
    "DateTime", [Date]+[Time]
)

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Greg_Deckler
Super User
Super User

Table = GENERATESERIES(DATE(2016,1,1),DATE(2020,12,31),.04166666666666666666666666666667)



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

@XaviOV you can also using Power Query , paste following code. There is start and end date which you can change as per your need.

 

let

    StartDate = #datetime(2020, 4, 1, 0, 0, 0),
    EndDate = #datetime(2020, 4, 30, 0, 0, 0),

    //Get the number of dates that will be required for the table
    GetDateCount = Duration.Days(EndDate - StartDate) + 1,

    //Take the count of dates and turn it into a list of dates
    GetDateList = List.DateTimes(StartDate, GetDateCount, #duration(1,1,0,0)),
    #"Converted to Table" = Table.FromList(GetDateList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "DateTime"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"DateTime", type datetime}})

in
    #"Changed Type"

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

fhill
Resident Rockstar
Resident Rockstar

It would be sugniffantly less overhead to build a Date Table with the Calendar funtion, and a seperate 24 hours in a day Time Table.  Can you go into more details of the end goal, maybe we can help come up with a better approach?

 

FOrrest




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.