Forum Discussion

mm_engstrom's avatar
mm_engstrom
New Member
8 years ago
Solved

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...
  • aviral's avatar
    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========

dean_mernagh's avatar
dean_mernagh
Regular Visitor
7 years ago
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's avatar
aviral
Advocate IV
7 years ago

Sorry for a late response. 

 

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

 

  • dean_mernagh's avatar
    dean_mernagh
    Regular Visitor
    7 years ago

    Sorry i can't see the image- can you explain please?

     

    Thank you.

     

  • aviral's avatar
    aviral
    Advocate IV
    7 years ago

    Have updated the image.

     

    You will have to go into File->Options and Settings->Options->Current File->Data Load

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi

    I have the same issue but in a measure.  My time intelligence button is ticked.

    Do you have any suggestions? I have been spending the last 3 days trying to get the year to date formula working but it is just not happening.

    Many thanks!! 

     

    Estimated Annualised Savings 2018 YTD =
    IF(
        ISFILTERED('Date'[Date].[Date]),
        ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
        TOTALYTD(
            [Estimated Annualised Savings 2018],
            'Current Month'[Date].[Date]
        )