cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Yin
Frequent Visitor

how to build a calendar table with date and time

Dear,

 

I learned how to build a calendar table by using the following formula:

Date =
ADDCOLUMNS (
CALENDAR (DATE(2000;1;1); DATE(2025;12;31))

But it only returns date by date. 

 

 

I want to build a more detailed date&time table, with 24 hours showed in this table,like the following picture, is there a way to do it in powerbi? Thanks!

 47c08ee2-47fe-4451-8ebe-7a9d700e4de9.png

1 ACCEPTED SOLUTION
v-ljerr-msft
Microsoft
Microsoft

Hi @Yin,

 

Based on my test, you should be able to use the formula below to create a calendar table with date and time. Smiley Happy

DateTime = 
ADDCOLUMNS (
    CROSSJOIN (
        CALENDAR ( DATE ( 2000, 1, 1 ), DATE ( 2025, 12, 31 ) ),
        UNION (
            ROW ( "Time", TIME ( 1, 0, 0 ) ),
            ROW ( "Time", TIME ( 2, 0, 0 ) ),
            ROW ( "Time", TIME ( 3, 0, 0 ) ),
            ROW ( "Time", TIME ( 4, 0, 0 ) ),
            ROW ( "Time", TIME ( 5, 0, 0 ) ),
            ROW ( "Time", TIME ( 6, 0, 0 ) ),
            ROW ( "Time", TIME ( 7, 0, 0 ) ),
            ROW ( "Time", TIME ( 9, 0, 0 ) ),
            ROW ( "Time", TIME ( 10, 0, 0 ) ),
            ROW ( "Time", TIME ( 11, 0, 0 ) ),
            ROW ( "Time", TIME ( 12, 0, 0 ) ),
            ROW ( "Time", TIME ( 13, 0, 0 ) ),
            ROW ( "Time", TIME ( 14, 0, 0 ) ),
            ROW ( "Time", TIME ( 15, 0, 0 ) ),
            ROW ( "Time", TIME ( 16, 0, 0 ) ),
            ROW ( "Time", TIME ( 17, 0, 0 ) ),
            ROW ( "Time", TIME ( 18, 0, 0 ) ),
            ROW ( "Time", TIME ( 19, 0, 0 ) ),
            ROW ( "Time", TIME ( 20, 0, 0 ) ),
            ROW ( "Time", TIME ( 21, 0, 0 ) ),
            ROW ( "Time", TIME ( 22, 0, 0 ) ),
            ROW ( "Time", TIME ( 23, 0, 0 ) ),
            ROW ( "Time", TIME ( 24, 0, 0 ) )
        )
    ),
    "DateTime", [Date] + [Time],
    "Hour", HOUR ( [Time] )
)

Note: after the table is created, Date and Time column will be formatted as Date/Time type, so you will need to change the Date column to Date type and Time column to Time type.

 

t1.PNG

 

Regards

View solution in original post

11 REPLIES 11
pade
Helper IV
Helper IV

I just created something equal to this, but with a granuality of seconds. This code could easily be changed to hours, minutes, quater of an hours, etc

 

A warning: Don't increase the date range to much if you also keep the granuality of seconds. This unless you have a lot of memory 🙂

 

DateTimeTable= SELECTCOLUMNS(
    CROSSJOIN(
        
        //************************************************************
        // Date range below. This will generate a table with a [Date] column
        //************************************************************
        CALENDAR(DATE(2018;01;01);DATE(2018;01;02));

        //************************************************************
        // Time granduality below. This will generate a table with a [Time] column
        //************************************************************
        SELECTCOLUMNS(
            CROSSJOIN(
                SELECTCOLUMNS(GENERATESERIES(0;59;1);"Second";[Value]);
                SELECTCOLUMNS(GENERATESERIES(0;59;1);"Minute";[Value]);
                SELECTCOLUMNS(GENERATESERIES(0;23;1);"Hour";[Value])
            );
            "Time"; TIME([Hour];[Minute];[Second])
        )
    );
    //************************************************************
    // Custom table columns below. 
    // This is extracted from the Date and Time columns generated above
    //************************************************************
    "Date"; FORMAT([Date]; "YYYY-MM-DD");
    "Time"; FORMAT([Time]; "hh:mm:ss");
    "DateTime"; [Date]+[Time]
)
Anonymous
Not applicable

Hi there, when I try the solution  it works but doesn't account for 8:00 AM.

 

When I try to add 8:00 AM I get dates from 1899.

 

Yes. By just checking the code, the complete line for 8 is missing. Just add that and you will be fine. But all of you that tries using this date-time table, please think twice before continuing cause you are probably on the way of doing a big design mistake. Instead read some blogs or patterns by the SQLBI hero’s at sqlbi.com and find a better solution
Yin
Frequent Visitor

Great thanks! I will try this method also!

v-ljerr-msft
Microsoft
Microsoft

Hi @Yin,

 

Based on my test, you should be able to use the formula below to create a calendar table with date and time. Smiley Happy

DateTime = 
ADDCOLUMNS (
    CROSSJOIN (
        CALENDAR ( DATE ( 2000, 1, 1 ), DATE ( 2025, 12, 31 ) ),
        UNION (
            ROW ( "Time", TIME ( 1, 0, 0 ) ),
            ROW ( "Time", TIME ( 2, 0, 0 ) ),
            ROW ( "Time", TIME ( 3, 0, 0 ) ),
            ROW ( "Time", TIME ( 4, 0, 0 ) ),
            ROW ( "Time", TIME ( 5, 0, 0 ) ),
            ROW ( "Time", TIME ( 6, 0, 0 ) ),
            ROW ( "Time", TIME ( 7, 0, 0 ) ),
            ROW ( "Time", TIME ( 9, 0, 0 ) ),
            ROW ( "Time", TIME ( 10, 0, 0 ) ),
            ROW ( "Time", TIME ( 11, 0, 0 ) ),
            ROW ( "Time", TIME ( 12, 0, 0 ) ),
            ROW ( "Time", TIME ( 13, 0, 0 ) ),
            ROW ( "Time", TIME ( 14, 0, 0 ) ),
            ROW ( "Time", TIME ( 15, 0, 0 ) ),
            ROW ( "Time", TIME ( 16, 0, 0 ) ),
            ROW ( "Time", TIME ( 17, 0, 0 ) ),
            ROW ( "Time", TIME ( 18, 0, 0 ) ),
            ROW ( "Time", TIME ( 19, 0, 0 ) ),
            ROW ( "Time", TIME ( 20, 0, 0 ) ),
            ROW ( "Time", TIME ( 21, 0, 0 ) ),
            ROW ( "Time", TIME ( 22, 0, 0 ) ),
            ROW ( "Time", TIME ( 23, 0, 0 ) ),
            ROW ( "Time", TIME ( 24, 0, 0 ) )
        )
    ),
    "DateTime", [Date] + [Time],
    "Hour", HOUR ( [Time] )
)

Note: after the table is created, Date and Time column will be formatted as Date/Time type, so you will need to change the Date column to Date type and Time column to Time type.

 

t1.PNG

 

Regards

View solution in original post

Loved it. Really!

The Dax code was really helpful and very easy to understand.

I'm getting an error "The expression refers to multyiple columns.  Multiple columns cannot be converted to a scalar value" when I try...

I think the problem might be how I do my date...my date is made instead of a preset date range, it goes from the minimum date in the table, to the max date based on invoice number.  Like so...

 

Calendar = CALENDAR(MIN(Invoice[NvDate]),max(Invoice[NvDate]))

 

then I modded the code suggested to so...

 

DateTime = 
ADDCOLUMNS (
    CROSSJOIN (
        CALENDAR(MIN(Invoice[NvDate]),max(Invoice[NvDate])),
        UNION (
            ROW ( "Time", TIME ( 1, 0, 0 ) ),
            ROW ( "Time", TIME ( 2, 0, 0 ) ),
            ROW ( "Time", TIME ( 3, 0, 0 ) ),
            ROW ( "Time", TIME ( 4, 0, 0 ) ),
            ROW ( "Time", TIME ( 5, 0, 0 ) ),
            ROW ( "Time", TIME ( 6, 0, 0 ) ),
            ROW ( "Time", TIME ( 7, 0, 0 ) ),
            ROW ( "Time", TIME ( 9, 0, 0 ) ),
            ROW ( "Time", TIME ( 10, 0, 0 ) ),
            ROW ( "Time", TIME ( 11, 0, 0 ) ),
            ROW ( "Time", TIME ( 12, 0, 0 ) ),
            ROW ( "Time", TIME ( 13, 0, 0 ) ),
            ROW ( "Time", TIME ( 14, 0, 0 ) ),
            ROW ( "Time", TIME ( 15, 0, 0 ) ),
            ROW ( "Time", TIME ( 16, 0, 0 ) ),
            ROW ( "Time", TIME ( 17, 0, 0 ) ),
            ROW ( "Time", TIME ( 18, 0, 0 ) ),
            ROW ( "Time", TIME ( 19, 0, 0 ) ),
            ROW ( "Time", TIME ( 20, 0, 0 ) ),
            ROW ( "Time", TIME ( 21, 0, 0 ) ),
            ROW ( "Time", TIME ( 22, 0, 0 ) ),
            ROW ( "Time", TIME ( 23, 0, 0 ) ),
            ROW ( "Time", TIME ( 24, 0, 0 ) )
        )
    ),
    "DateTime", [Date] + [Time],)

But i'm still getting an error...now "Function ADDCOLUMNS expects a column name as argument number 4.

 

 

Please help 🙂

This might be better:

DateTime = 
ADDCOLUMNS (
    CROSSJOIN (
        CALENDAR(DATE(2018,01,01),DATE(2018,01,01)),
        SELECTCOLUMNS(GENERATESERIES(0,23,1),"Time",TIME([Value],0,0))
    ),
    "DateTime", [Date] + [Time]
)

 or:

DateTime = 
ADDCOLUMNS (
    CROSSJOIN (
        CALENDAR(MIN(Invoice[NvDate]),max(Invoice[NvDate])),
        SELECTCOLUMNS(GENERATESERIES(0,23,1),"Time",TIME([Value],0,0))
    ),
    "DateTime", [Date] + [Time]
)

You only need to replace the last ","

 

change:

"DateTime", [Date] + [Time],)

 

To:

"DateTime", [Date] + [Time])

Yin
Frequent Visitor

Hello @v-ljerr-msft, It's perfect. Thanks a lot!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.