Unable to properly convert a Date/Time column to a calendar date field

[bondrood]

Hello,

I currently have a column in MM/DD/YYYY HH:MM:SS format, but it's not recognized as a calendar date field. When attempting to directly convert it using the table tools "Mark as date table," I'm unable to set it as such, with an error message of "The date column must have unique values." With other time columns present in the dataset there are non-unique values, and they are recognized as calendar date fields. The column itself is recognized as a Date/time column with the format mentioned previously, but not as a calendar date field. Any advice on how to convert this column to a calendar date field? Let me know if I need to provide any additional examples, I'm working with a private database but I will try to share as much as I can.

TYVM in advance.

[danextian · Accepted solution]

Hi @bondrood 

Using a fact table as a dates table is not considered good practice. Similarly, keeping a datetime column can lead to high memory usage due to its high cardinality. It's more efficient to split it into separate date and time columns. If the seconds aren't needed, it's best to remove them as well. Perform this split in the query editor.

Once loaded, create  a calendar table in DAX or M.  Here's a how to do a DAX calculated calendar table.

Calendar = 
ADDCOLUMNS (
    CALENDAR ( MIN ( FactTable[Date] ), MAX ( FactTable[Date] ) ),
    "Year", YEAR ( [Date] ),
    "YYYYMM", FORMAT ( [Date], "YYYYMM" )
)

Create a one-to-many single direction relationship from 'Calendar'[Date] to FactTable[Date]. Set the calendar table as a date table.

[MasonMA]

@bondrood, This indeed reduces the cardinality of the source model!

However, it's also based on the assumptions that you are on an Import Mode/at least Composite model with an Import table that has this Datetime column and you can transform data in Power Query (which was not clearly mentioned in your post...🤔). Sorry many of my reports were based on DirectQuery due to Data policy at my work, haha. 

[bondrood]

That's my bad! I definitely should've included it, I don't have much experience so I wasn't entirely sure if I should've included it. Thank you for letting me know if I should be including it in the future!

[Thejeswar]

Hi @bondrood ,

To set a table as a Data table in Power BI, the Date Column should have unique values. i.e. The Table should have one record per date.

In your case, you said it is a DateTime column and has multiple records. So I am assuming the column should have both date and time value in it.

If you want to have a pure Date table, then you need to split this DateTime Column to two columns in Power Query as Date and Time. Then further use this new table to get to your calendar table.

Option 1: If time value is not required

Step 1: Remove the time column

Step 2: Remove table duplicates

The Table will be a Date table with one record per date

Option 2: If time is required

Using this table create a new Date Table in Power BI using DAX

[Ashish_Mathur]

Hi,

In Power Query, split that column into 2 - Date and Time.  Then write this DAX caalculated column formula

Calendar = calendar(min(Data[Date]),max(Data[Date]))

Create a relationship (Many to One and Single) from the Date column of the Data table to the Calendar table.

Hope this helps.

[MasonMA]

@bondrood 

Hi, Power BI expects a Date Table to have one row per day and your Column may have same date appear multiple times.

So you would need to

1.Create a new Date table either in Power Query or through DAX. Example with DAX sample code below

DateTable = 
ADDCOLUMNS (
    CALENDAR (DATE(2010,1,1), DATE(2030,12,31)),
    "Year", YEAR([Date]),
    "Month", FORMAT([Date], "MMMM"),
    "MonthNumber", MONTH([Date]),
    "Day", DAY([Date]),
    "Weekday", FORMAT([Date], "dddd"),
    "YearMonth", FORMAT([Date], "YYYY-MM")
)

Then Mark this new Date table as your date table in Model view. 

2.Extract your Date from your 'column in MM/DD/YYYY HH:MM:SS format' with Calculated Colunm

DateExtract =DATEVALUE([DateTimeOriginal])

3. Use DateExtract to build relationships or connect to your separate new date table for reporting. 

hope this helps~