Date column with date data type displays multiple values in filter

[TomSinAA]

I have a date value (TMS_ACTUAL_DELIVERY_DATE) in my data table with data type as DateTime.  I created another column (Delivery Date) using the date value column and changed it's data type to Date.  However the filter for the new column looks like it's treating the values as DateTime and not Date only.

Fitler shows multiple lines for 8/28/24 on Delivery date.  It should only list one since the column is a Date type and not DateTime.

This appears to be impacting the join of the Delivery Date column to the Calendar table which was created with this script:

create_date =

ADDCOLUMNS (

CALENDAR (DATE(2023,1,1), DATE(2050,12,31)),

"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),

"Year", YEAR ( [Date] ),

"Monthnumber", FORMAT ( [Date], "MM" ),

"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),

"YearMonth", (Year([Date]) * 100 ) + MONTH([Date]),

"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),

"MonthNameShort", FORMAT ( [Date], "mmm" ),

"MonthNameLong", FORMAT ( [Date], "mmmm" ),

"DayOfWeekNumber", WEEKDAY ( [Date] ),

"DayOfWeek", FORMAT ( [Date], "dddd" ),

"DayOfWeekShort", FORMAT ( [Date], "ddd" ),

"Start of Week", [Date]  - WEEKDAY([DATE],1),

"End of Week", [Date] + 7 - WEEKDAY([DATE],1),

"Quarter", "Q" & FORMAT ( [Date], "Q" ),

"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ))

[v-nmadadi-msft · Accepted solution]

Hi  @TomSinAA,
Thanks for reaching out to the Microsoft fabric community forum.

To enable consistent date filtering across your data model, you should create a dedicated date dimension table and establish one-to-many relationships from this calendar table to the date fields in your other tables, with the calendar table on the 'one' side. This allows you to apply filters to the shared calendar table, which will then propagate through the model via these relationships.

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards

[DataInsights]

@TomSinAA,

Try defining Delivery Date as follows:

Delivery Date =
INT ( Movements[TMS_ACTUAL_DELIVERY_DATE] )

[TomSinAA]

It worked by creating the delivery date column using the Date function (i.e. Yr, Month, Day) rather than setting the data type on the new column. 

[uzuntasgokberk]

Hello @TomSinAA ,

Why don't you make relationship to your DimDate instead of joining? İf you'd like to "Table view" in your PBI --> Click "New Table" --> paste your create_date script --> make relationship dimdate and facttable by date column (one to many). In you filter side you'll see only unique date's. That is best practice.

Best Regards,
Gökberk Uzuntaş

LinkedIn: https://www.linkedin.com/in/g%C3%B6kberk-uzunta%C5%9F-b43906198/

Medium: https://medium.com/@uzuntasgokberk

İf this post helps, then please consider Accept it as solution and kudos to help the other members find it more quickly.

[TomSinAA]

I did create the relatationship.  However it did not work until I created the delivery date column in the data table using the Date function rather than setting the data type to Date only.