Forum Discussion

dmatliak's avatar
dmatliak
Frequent Visitor
4 years ago
Solved

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 for...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

     

     

    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's avatar
amitchandak
Super User
4 years ago

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

dmatliak's avatar
dmatliak
Frequent Visitor
4 years ago

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.