Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
dmatliak
Frequent Visitor

Incorrect date format when importing data from Power BI dataset

I have a problem with format of my Date field. The field is recognized as date field, it shows values chronologically, but I can not use the date field as hierarchies. The date value is showed in format Month.Day.Year 12:00:00. For example June 30th 2022 would be shown 06.30.22 12:00:00. I can not format the field because I import data via Power BI dataset and this option is not available. Anozher issue is that I can not create any measure where with date functions (for example Year-to-Year comparison or rolling average) where date hierarchies must be used. Anyone can help with this? Thanks a lot.

1 ACCEPTED SOLUTION
v-eqin-msft
Community Support
Community Support

Hi @dmatliak ,

 

The date hierarchy will work for you when the below conditions are met:

  • Enable automatic date and time
  • The table storage mode is Import
  • The column data type is date or date/time
  • The column isn't the "many" side of a model relationship

For more details, please kindly refer to the blog:

Why there is no date hierarchy - Microsoft Power BI Community

 

 

When you connect to Power BI datasets, the default mode is Live connection, which prohibits changing the data type/data format/ create custom hierarchy.

Eyelyn9_1-1656998607894.png

 

 

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-eqin-msft
Community Support
Community Support

Hi @dmatliak ,

 

The date hierarchy will work for you when the below conditions are met:

  • Enable automatic date and time
  • The table storage mode is Import
  • The column data type is date or date/time
  • The column isn't the "many" side of a model relationship

For more details, please kindly refer to the blog:

Why there is no date hierarchy - Microsoft Power BI Community

 

 

When you connect to Power BI datasets, the default mode is Live connection, which prohibits changing the data type/data format/ create custom hierarchy.

Eyelyn9_1-1656998607894.png

 

 

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

@dmatliak , if you have date table, joined with your date. You should use time intellignece. Using date hierarchy is not a good idea 

 

Example measures

 

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

 

 

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

 

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Hi, thanks for you quick reply. But there is a problem with formate of my date field ("leave_date"). It is not formatted properly as date. And I can not format is because I import data from Power BI dataset. See below, I wanted to calculate YTD number of lost clients, but it did not work properly because "leave_date" is not a date field.

 

dmatliak_0-1656576675301.png

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.