Date Fields Appearing as Text and Not Grouping Properly in Excel Filters (SSAS Tabular Model)

Question

We are experiencing an issue with our SSAS Tabular model where fields defined as "Date" are being recognized as "Text" when connected to via Excel. This behavior impacts the ability to use Excel's date functionalities, such as filtering, sorting, and especially grouping by date, which does not work as expected since the fields are treated as text.

Key details:

Tabular Model: SSAS Tabular, with the affected fields set as "Date" in the model;

Excel Version: Microsoft 365;

Connection Type: Excel connecting via the default OLAP connection;

Expected Behavior: Date fields should be recognized as dates in Excel, allowing proper date-based grouping in filters;

Observed Behavior: Date fields are displayed as text, preventing correct grouping and requiring manual conversion within Excel.

We have already verified the data types in the model and tried refreshing the connection, but the issue persists. Please advise on a resolution for this issue.

Thank you!

Answer 1

Hi，in.prc

Hello, this is a known issue with the Excel product, which has not been solved

and someone has already created an idea for it, details can be found at: https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=e9b77e43-84bc-4440-9e3f-0725dcf60cd3&%3Bpage=4&page=1.

Here are a few ideas that might do the trick：

Create a manual date table using the DAX function "Table = CALENDARAUTO()". Refer to

https://community.fabric.microsoft.com/t5/Service/Analyze-in-Excel-feature-Date-are-not-recognized-in-Excel-file/m-p/1551413

Change the source table's date format.Refer to

https://learn.microsoft.com/en-us/archive/msdn-technet-forums/c0253620-6ae0-46ea-8b6e-124e6c950239

Build a calculated column in date format.Refer to

https://stackoverflow.com/questions/18598408/excel-import-data-from-analysis-services-date-comes-across-as-text

Regards

Mikey Qiao

---

If you're satisfied with the answer, don't forget to "Accept it," as this will help others who have similar questions to yours.