Time intelligence does not work

[mm_engstrom]

Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),

How does time intellingence work on a data warehouse with own data hierachy or what does primary dat column mean?

[buckeyepilot67]

Been having issues trying to calculate a rolling 14 day average.  Getting the same error trying to create a measure multiple ways.

[Anonymous]

I had the same error others had, and the following auto generated code worked fine for me, after I *cleared my filters* of time-based criteria.  

_INDY rolling average 2 =

IF(

    ISFILTERED('Daily Kit Count - RCV'[_ WeekEnding]),

    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),

    VAR __LAST_DATE = LASTDATE('Daily Kit Count - RCV'[_ WeekEnding].[Date])

    RETURN

        SUMX(

            DATESBETWEEN(

                'Daily Kit Count - RCV'[_ WeekEnding].[Date],

                DATEADD(__LAST_DATE, -1, DAY),

                __LAST_DATE

            ),

            CALCULATE(SUM('Daily Kit Count - RCV'[_INDY]))

        )

)

[Anonymous]

For anyone still encountering this issue, this fix worked for me. In retrospect this is probably quite obvious to the experienced users here, but for those of us still learning it isn't quite so clear.

Assuming you have created a custom date dimension, right click on the table name in your fields list. Navigate down to "Mark as date table" and make sure it is checked. Now I no longer recieve the error at the top of the thread.

[Anonymous]

I did this but message " The Date Column Can't have gaps in dates" and I am sure there are no gaps

[aviral]

I am facing the same issue. 

Getting the following error:

Time intelligence quick measure can only be grouped or filtered by the power-bi provided date hierarchy or primary date column

When I try to convert the column to date format it again throws the following error: 

We cannot automatically convert the column to date type. The column is being calculated from an existing date column and calculation looks like this: 

RfS Date =
VAR filteredTab = SUMMARIZE(FILTER(myTable, <<expression>>,myTable[DDate],"abcDate",myTable[DDate])
RETURN if(COUNTROWS(filteredTab)>0,MINX(filteredTab,[abcDate]),BLANK())

=========

I am also facing issue in converting the data type of any column from one type to another. This issue was not there in older versions. 

I also opened a ticket with them but doesn't seem to be working out. 

I am not sure but this seems to be a bug. 

[aviral · Accepted solution]

Got a solution from PBI support. 

========SOLUTION TEXT STARTS========

One easy way to solve the ussie is to transform the date from a text to a date value using the UK format:

let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fVRRcsUgCLxLvjsjYIzmLJl3/2tUGht3ie2fDALLsnBdW9YkOZnosX2+rq2/b7P+mFmSnNPbTbVp6p5k589o9sxtmnYO7x1rNamAaUkVzJbEZmx/S4FUQiDde4C3cCGODf32ovjZG0QYQrFaBhuj7jG8a3IOQiV7LHR7x+dzcPWQI7rAPFI1guFeZXJWsZMNgPEQO2BkBqmMqr3mC6i8BeBKAlfGQgqx/Fm5bpcZSkUbNRhEqCwzhwGpfAoVUhWObaRYBwnkOO2V2QCJ2q939FtfTJZF3SkzYxh10dEwhToKG/rmmTR5siYZZCQnaLKwzCphDgNdaANbMDKDroRX0jVZmEnWBtGe+W4odRRQ9bq47GH3+5vMzCDfV+XvI+MwYNnDJQw89/HhBMOdjKdAaXF8KCezAXXjuHk1/F7xqaeRFa57EDmO2f5lA2b0HNUe+/kG", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t]),

    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-GB")

in

    #"Changed Type with Locale"

In the Query Editor > Advanced query editor, you have to replace your

#"Changed Type with Locale"= ……………………………….” (whatever is there in middle), please replace this with the below one and close and apply and now run the DAX query will work

#”Changed Type with Locale” = Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-GB")

========SOLUTION TEXT ENDS========

[Veree]

First you will have to make sure the Date column is not blank.

Then you can use the DAX from the example.

3 Months rolling average =

IF(

    ISFILTERED('SALES'[Date]),

    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),

    VAR __LAST_DATE = ENDOFMONTH('SALES'[Date].[Date])

    VAR __DATE_PERIOD =

        DATESBETWEEN(

            'SALES'[Date].[Date],

            STARTOFMONTH(DATEADD(__LAST_DATE, -2, MONTH)),

            __LAST_DATE

        )

    RETURN

        AVERAGEX(

            CALCULATETABLE(

                SUMMARIZE(

                    VALUES('SALES'),

                    'SALES'[Date].[Year],

                    'SALES'[Date].[QuarterNo],

                    'SALES'[Date].[Quarter],

                    'SALES'[Date].[MonthNo],

                    'SALES'[Date].[Month]

                ),

                __DATE_PERIOD

            ),

            CALCULATE(SUM('SALES'[Invoiced Quantity]), ALL('SALES'[Date].[Day]))

        )

)

[Anonymous]

After Doing this, it still give the message "

ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column.")"

[Anonymous]

[Anonymous]

I have this error and I am sure that column is exist identically by name

[jayanthan]

Hi 

i get following error, please advise

Jayanthan

[aviral]

Hi @jayanthan

Could you please post the M code from the query because it seems there is some error in the way locale setting is being applied.

[dean_mernagh]

Hi @jayanthan, Do you mean the below? Appreciate your help... Sales _Volume YTD = IF( ISFILTERED('tbl_Sales'[Date]), ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."), TOTALYTD(SUM('tbl_Sales'[Sales _Volume]), 'tbl_Sales'[Date].[Date]) )

[Anonymous]

Having an issue here as well. I've created a separate date mapping table, using PowerBI's date foremat and is linked with the data. However the error shows: Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column

[aviral]

Hi @Anonymous

Please post some piece of code and/or screenshot of relationship table to understand the issue. 

[dean_mernagh]

"Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."

It's all i've got back for a full day of trying and i can't for the life of me figure it out- i have tried all suggestions and nothing is working.

Can ANYBODY help PLEASE???

[Anonymous]

I have the same issue. Any news?

[aviral]

Hi @dean_mernagh

Please post some screenshots to confirm that the data type for the column in question is date/time.

[dean_mernagh]

Hi @aviral, Appreciate your help, i can't post a screenshot as it won't let me but i did format as a date and also ensured that the locale was also on the correct setting. Please see below for code. Sales _Volume YTD = IF( ISFILTERED('tbl_Sales'[Date]), ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."), TOTALYTD(SUM('tbl_Sales'[Sales _Volume]), 'tbl_Sales'[Date].[Date]) )

[aviral]

Sorry for a late response. 

Make sure in the settings "Time Intelligence" is ticked as shown below.