Forum Discussion

JCrilly's avatar
JCrilly
Frequent Visitor
7 years ago

Date Hierarchy for Quick Measure Not Working

I defined a date hierarchy but the option appears greyed out and when i try to drag the columns over nothing happens..    
Need Help
Brysonds's avatar
Brysonds
Helper III
7 years ago

Hi!

 

I think I see the source of confusion here.

 

You created an 'artificial' hierarchy from the field menu using some fields.

 

You actually have to set the property of the field in the query mode to "Date" and Power BI will create the date hierarchy for you.

 

Notice the icon of the field shows a calendar (indicating it's now a date field):

 

       

 

When you add a date field to your table, matrix, etc. It will show the 'hierarchy' by default like this (tip: click the dropdown arror to change back to actual date instead of hierarchy):

 

 

Once you use a date field, you should be able to use the time-intelligence in the quick measure.

 

JCrilly's avatar
JCrilly
Frequent Visitor
7 years ago

Thanks,

I am using Analysis Services Tabular as the datasource.

The column is defined as a date in the model however power BI is not recognizing it

  • v-danhe-msft's avatar
    v-danhe-msft
    Microsoft Employee
    7 years ago

    Hi JCrilly,

    Based on my test, the Date column could work on my side:

    Could you please check the version of the Power BI Desktop(2.63.3272.40262 64-bit (October 2018)) and test it again?

     

    Regards,

    Daniel He

  • Brysonds's avatar
    Brysonds
    Helper III
    7 years ago

    Can you show a picture of your date column in the query view like I did? Is it formatted to date? Is it the full date (mm/dd/yyyy)? Dending on the date format, you may have to create a separate "date table" in Power BI and set-up a relationship betwen your date column and the date table, then use the date table in your visuals/reporting. 

     

    You can create a date table in Powe BI by going to the "Modeling" tab in the ribbon, selectin "New Table" and pasting this code in. Hope this helps.

     

    Date =
    ADDCOLUMNS (
    CALENDAR (DATE(2000,1,1), DATE(2025,12,31)),
    "Year", YEAR ( [Date] ),
    "Monthnumber", FORMAT ( [Date], "MM" ),
    "YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
    "YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
    "MonthNameShort", FORMAT ( [Date], "mmm" ),
    "MonthNameLong", FORMAT ( [Date], "mmmm" ),
    "DayOfWeekNumber", WEEKDAY ( [Date] ),
    "DayOfWeek", FORMAT ( [Date], "dddd" ),
    "DayOfWeekShort", FORMAT ( [Date], "ddd" ),
    "Quarter", "Q" & FORMAT ( [Date], "Q" ),
    "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ))

    • JCrilly's avatar
      JCrilly
      Frequent Visitor
      7 years ago

      When you use SSAS you don't have access to the underlying query..

      version is correct

       

      • Brysonds's avatar
        Brysonds
        Helper III
        7 years ago

        I am not talking about the SSAS query, I am talking about the query in Power BI. If you are importing data from a data source into Power BI, it automatically creates a query where you can apply ETL. 

         

        If you aren't importing the data and instead are doing a live connection, then I am afraid there isn't anything I can help with.