How did you build a calendar table from a datetime column?

[Chris123Terr]

I want to create a calendar using DAX using the min and max of this column

But when I create it, the calendar comes out with this format and it does not establish a relationship when I try to make graphs

[PaulDBrown]

Best Practices recommwnd seperating date/Time fields into 2 columns: one for date and the other for time. In Power Query, select the column, go to add columns and select Date and choose Date. The do the same for Time. You can then create separate dimension tables for Date and Time

[Chris123Terr]

Hello. Thanks for the reply. I have some additional questions.

In this case, would I have two calendar tables? That is, a table where the date "dd/mm/yyyy" is stored and another table where the time "hh:mm:ss" is stored?

Could a calendar table be made that stores both date and time and can be related to the fact table?

[PaulDBrown · Accepted solution]

"Could a calendar table be made that stores both date and time and can be related to the fact table?"

You would need a  massive dimension table with days * 24 (hours) * 60 (minutes) and possible * 60 (seconds).

So a single day would contain between 1,440 and 86,400 rows (depending on whether you need granularity down to minutes or seconds)...

That is why it is recommended to split date and time into two separate tables

"In this case, would I have two calendar tables? That is, a table where the date "dd/mm/yyyy" is stored and another table where the time "hh:mm:ss" is stored?"

Correct. You need a dimension table for date and one for time. With regards Time, you need to decide the level of granularity in the model (basically decide whether you will need to show/calculate down to the level of seconds) since the size of the table grows of course...Minutes = 1,440 rows; seconds 86,400 rows

This is one way of modeling Date/Time. In Power Query, add columns two new columns for Date and Time Values:

Make sure each column is set the the correct data type (date, time, number...).

In theory you can delete the original Date/Time column.

Now you can create the dimension tables using DAX as follows:

Calendar Table =
ADDCOLUMNS (
    CALENDAR ( MIN ( fTable[Date] ), MAX ( fTable[Date] ) ),
    "MonthNum", MONTH ( [date] ),
    "Month", FORMAT ( [Date], "MMM" ),
    "Year", YEAR ( [Date] )
)

and 

For time including seconds:

Time in Seconds =
VAR _hours =
    SELECTCOLUMNS ( GENERATESERIES ( 0, 23, 1 ), "@Hour", [Value] )
VAR _minutes =
    SELECTCOLUMNS ( GENERATESERIES ( 0, 59, 1 ), "@Minutes", [Value] )
VAR _seconds =
    SELECTCOLUMNS ( GENERATESERIES ( 0, 59, 1 ), "@Seconds", [Value] )
VAR _MinSecs =
    CROSSJOIN ( _minutes, _seconds )
VAR _HoursMinsSecs =
    ADDCOLUMNS (
        CROSSJOIN ( _hours, _MinSecs ),
        "@TIME", TIME ( [@Hour], [@Minutes], [@Seconds] )
    )
RETURN
    ADDCOLUMNS (
        SELECTCOLUMNS ( _HoursMinsSecs, "Time", [@TIME] ),
        "Minutes", TIME ( 00, MINUTE ( [Time] ), 00 ),
        "Hour", TIME ( HOUR ( [Time] ), 00, 00 )
    )

For minutes (you will need the Time column in the Fact table to be formatted as hh:mm):

Time in Minutes =
VAR _hours =
    SELECTCOLUMNS ( GENERATESERIES ( 0, 23, 1 ), "@Hour", [Value] )
VAR _minutes =
    SELECTCOLUMNS ( GENERATESERIES ( 0, 59, 1 ), "@Minutes", [Value] )
VAR _HoursMins =
    ADDCOLUMNS (
        CROSSJOIN ( _hours, _minutes ),
        "@TIME", TIME ( [@Hour], [@Minutes], 00 )
    )
RETURN
    ADDCOLUMNS (
        SELECTCOLUMNS ( _HoursMins, "Time", [@TIME] ),
        "Minutes", TIME ( 00, MINUTE ( [Time] ), 00 ),
        "Hour", TIME ( HOUR ( [Time] ), 00, 00 )
    )

IMPORTANT: make sure all the columns in all tables are formatted to their corresponding data type

You can now create the relationships between the corresponding fields in the tables:

I've attached the sample PBIX file