Add time (clock, aka hours and minutes) to my calender table

[unclejemima]

I've got a calender table with a bunch of DAX foruma's...like date, year, quarter, month, monrht sort, etc.

I'd like to make a new column for "time", specifically hours and minutes.  Can anyone help me with a DAX forula so I can have a Time column with hour and minute??

Thanks!

[unclejemima]

Example, my first DAX in my calender table is

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

Here is a screenshot of the other columns.  Would like to have one for time, and if possible hours and minutes in their own column.

[unclejemima]

Anyone???  Please 🙂

[unclejemima]

Is this a crazy  complicated thing to do or am I the first one to ever ask such a thing???!?!?!

Help :-)!!!

[thomasronn]

Have a look at this post https://community.powerbi.com/t5/Desktop/how-to-build-a-calendar-table-with-date-and-time/td-p/24172... 

[unclejemima]

Almost.  I tried the formula but get this error...

"The expression refers to multyiple columns.  Multiple columns cannot be converted to a scalar value"

Any idea's?

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] )
)

[unclejemima]

Here is a snip of the error...not sure if looking at the other columns I have might help ID the issue?

[unclejemima]

Anyone?  I'm really trying to get this solved and keep hitting road blocks.

Am I supposed to paste the formula in the advanced editor, not the column/dax formula bar???

[unclejemima]

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 🙂