Time intelligence does not work

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?

aviral
8 years ago
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========

24 Replies

Anonymous
Not applicable
7 years ago
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
  Not applicable
  5 years ago
  I did this but message " The Date Column Can't have gaps in dates" and I am sure there are no gaps
ErikHorsthuis
Regular Visitor
8 years ago
I have the same problem. Using Quick measure with Time Intelligence generates Dax-function with this error-message. I have no problem in creating manually the Dax-function for for example TotalYTD. I have a date-table with correct date-cllumn as primary key. So why is the Quick measure not working? I use the latest Power BI Desktop version, I downloaded today.
aviral
Advocate IV
8 years ago
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
  Advocate IV
  8 years ago
  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========
  - jayanthan
    Helper III
    8 years ago
    Hi
    
    i get following error, please advise
    
    Jayanthan
Anonymous
Not applicable
6 years ago
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]))
        )
)
buckeyepilot67
New Member
5 years ago
Been having issues trying to calculate a rolling 14 day average. Getting the same error trying to create a measure multiple ways.