Forum Discussion

iowakrz's avatar
iowakrz
Frequent Visitor
7 years ago
Solved

Date Hierarchy in Direct Query

Is there a solution to getting the date hierarchy in Direct Query?  I noted some postings saying it was not available but they are from 2016.  I'm hoping there has been a solution devised since then....
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    Hi iowakrz ,

     

    Currently, the built-in date hierarchy is not yet available when using DirectQuery mode. You could click to upvote this idea or add your own comments.

     

    As a workaround, you could create a custom date hierarchy manually. Please create [Year], [Quarter], [Month], [Day] columns first, right-click the original Date column and choose 'New Hierarchy', then, drag [Year], [Quarter], [Month], [Day] columns to place them under [Date] column.

     

    Best regards,

    Yuliana Gu

Anonymous's avatar
Anonymous
Not applicable
7 years ago

@Yuliana Gu, how do you go about creating the [Year], [Quarter], [Month] and [Day] column?  I am new on DAX, so what I did is select the table, and select New Column and when I entered [Year], there is an error message, enclose entire name in brackets.

 

thanks.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
7 years ago

Hi,

Year = Year(Calendar[Date])

Month Number = Month(Calendar[Date])

Month Name = FORMAT(Calendar[Date],"mmmm")

Day = Day(Calendar[Date])

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Ashish_Mathur ,

     

    I'm unable to use the following measure while my PowerBI using DirectQuery

     

    Month Name = FORMAT(Calendar[Date],"mmmm")

     

    Can you suggest someother method to get the month name?

     

    Also, kindly suggest few methods to find the quarter of the year as well.

     

    Thanks in advance.

     

    Regards,

    Param

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Ashish_Mathur, when I entered, the error message is cannot find table 'calendar'.  Am I suppose to enter as Year=Year (mytable[mystimestamp])?  My timestamp format is for example: 12/3/2018 12:00:00 PM.  Also, how do you create the Quarter?

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      7 years ago

      Hi,

       

      Yes.  Try this calculated column fomula

       

      Year=Year(mytable[mystimestamp])