PowerBi doesn't honour an SSAS OLAP cube date field

Question

We have an SSAS OLAP cube, in production since long and perfectly tested by dozens of users, with a normal Time dimension (two hierarchies, Months and Weeks, but this is irrelevant).

The Time dimension key is a date field. On the data view it is defined as DataType: System.DataTime. On the dimension as Calendar -> Date, Usage: Key.

Using this date field on an Excel table accessing the OLAP cube is fully operational, it is a date and "Date filter" options are available as expected.

But trying to use this field on PowerBI defeated all our efforts! No way to have PowerBI interpret the field as a date, so no date filters are available. PowerBI thinks it's a text field and nothing we can think makes it change its behaviour.

We tested PowerBI with an external Excel and adding the SSAS OLAP dimension so to be able to modify the field format and oh surprise! the field is interpreted as text. Changing the format to date makes it work. But there is no way to change the format for an SSAS OLAP cube when accessed directly as the primary (and only) source of data for the PowerBI repport.

Any idea how to define an SSAS OLAP date field so PowerBI understands it is a date?

Answer 1

Found it! One minute before getting mad forever I spotted the diference.

I was able to create two dimensions on the same cube, with the same field. One works, the other doesn't.

Define the date field as Order by Key and PowerBi treats it as a date.

Define the date field as Order by Name and PowerBi treats it as text.

Answer 2

I came across same situation. I don't know OP's calendar dimension properties, but his solution does not helped me.

In dimension design I used ValueColumn property

And in Power BI I saw a new field, that worked pretty nice with Slicer visual:

And Excel all remained the same: