cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
XaviOV Helper III
Helper III

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

Accepted Solutions
Microsoft
Microsoft

Re: Create table with date and time

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

Microsoft
Microsoft

Re: Create table with date and time

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
fhill Solution Sage
Solution Sage

Re: Create table with date and time

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

Super User IV
Super User IV

Re: Create table with date and time

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


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User IV
Super User IV

Re: Create table with date and time

@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"

 






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Microsoft
Microsoft

Re: Create table with date and time

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

XaviOV Helper III
Helper III

Re: Create table with date and time

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

Microsoft
Microsoft

Re: Create table with date and time

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

Helpful resources

Announcements
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors