Date format issue between PowerBI Desktop and semantic model built on lakehouse sql endpoint

[wilson_smyth]

I have a lakehouse and have built a semantic model on top of this.
I have built a power bi desktop report against this semantic model.

When i drag the dimDate date field to a slicer on the report all the visuals break and i get the error:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Its one of the many stumbling blocks ive hit while trying use fabric, which appears to be very buggy.

On my powerbi desktop, the date is in the format of 13/01/2024, which looks to match the date column value in my dimdate table in my lakehouse.

Not sure what needs to be done here, appreciate any help and advice.

Can anyone advise how i resolve this?

[v-huijiey-msft]

Hi @wilson_smyth ,

Is my follow-up just to ask if the problem has been solved?

If so, can you accept the correct answer as a solution or share your solution to help other members find it faster?

Thank you very much for your cooperation!

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[v-huijiey-msft]

Hi @wilson_smyth ,

Thanks for the reply from lbendlin .

If you are storing date data in Lakehouse using the varchar type instead of the datetime type, this may cause you to encounter data conversion errors in Power BI.

Please check if the date columns in the dimDate table are stored as datetime data type, if they are stored as varchar, you need to convert them to datetime.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[lbendlin]

pretty sure the system somewhere tries to interpret that as a Murican Date. 

Better to use ISO 8601 throughout to avoid ambiguities like this.