Forum Discussion

jn32324's avatar
jn32324
New Member
8 years ago
Solved

Time intelligence quick measure error with calculated date dim table and Amazon Redshift fact table

I'm trying to figure out how to use a Time Inteligence Quick Measure for year-over-year change (YoY% increase) with our Amazon Redshift data. I've read as many posts as I can but continue to receive the same error: ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column.")

 

Power BI Desktop Version: 2.53.4954.481 64-bit (December, 2017)

 

The fact table has a single relationship to the calculated date dim table with a date field (timestamp data type) (I've also tried to set the relationship to both but get the same error).

 

I created a calulated date dim table using the following formula to ensure I always cover the entire date range of the fact table:

CalendarDate = CALENDAR(startofyear('fact'[processing_date]),ENDOFYEAR('fact'[processing_date]))

When trying to create a Time Inteligence Quick Measure for year-over-year change I get the following error:

 

amount YoY% = 
IF(
	ISFILTERED('CalendarDate'[Date]),
	ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),	VAR __PREV_YEAR =
		CALCULATE(
			SUM('fact'[amount]),
			DATEADD('CalendarDate'[Date].[Date], -1, YEAR)
		)
	RETURN
		DIVIDE(
			SUM('fact'[amount])
				- __PREV_YEAR,
			__PREV_YEAR
		)
)

I confirmed the data is setup correctly in Redshift and all dates have a data type of Date (I tried datetime as well).

 

I am looking to use the out of the box quick measures as the solution to this problem so that we can easily scale this solution across all of our reports and dashboards. 

 

Thank you in advance for your help troubleshooting this issue with me.

  • jn32324's avatar
    jn32324
    8 years ago

    MarkS When I added that measure, it incorrectly returned the sum total for that year, not the previous year.

     

    However, when I changed it to this measure with previuosyear() as the function, it correctly displayed the previous year.

    Prev_year = CALCULATE(
			SUM(fact[amount]),
			PREVIOUSYEAR(CalendarDate[Date])
		)

     

5 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi,

     

    Try this

     

    1. The formula for creating the CalendarDate Table should be

    =CALENDAR(MIN('fact'[processing_date]),MAX('fact'[processing_date]))

     

    1. In the Calendar Table, extract Year from the Date column by using

    =YEAR(CalendarDate[Date])

     

    1. Create a relatioship from the Procssing_Date column of your Fact table to the Date column of your CalendarDate Table
    2. Drag Year from the CalendarDate Table to your visual
    3. Write this measure

    =SUM('fact'[amount])/CALCULATE(SUM('fact'[amount]),PREVIOUSYEAR(CalendarDate[Date]))

     

    Hope this helps.

  • MarkS's avatar
    MarkS
    Resolver IV
    8 years ago

    Hi jn32324

    Are you getting the error when using the measure, or are you worried that when creating the Quick Measure it puts that text in the error function that will be shown if the measure is used without a valid Date hierarchy?

     

    • jn32324's avatar
      jn32324
      New Member
      8 years ago

      MarkS I'm getting this error as soon as I create the measure as well as when I try to use it in a tile visual as the value. 

       

      I also get 0% when using it in a matrix (example below) when filtering on the dim calendar date range.

       

      Year Sum Amount YoY%

      20151,0000%
      20162,0000%
      20173,0000%
  • MarkS's avatar
    MarkS
    Resolver IV
    8 years ago

    Hi jn32324

    This line in the formula is not an error message

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

    but is a function that will show the quoted text when there is no Date field. 

     

    I don't see why you are getting the 0% in the matrix though.  Could you try creating a measure (recreates the var that the quick measure uses)

    	Prev_Year = CALCULATE(
			SUM('fact'[amount]),
			DATEADD('CalendarDate'[Date].[Date], -1, YEAR)
		)

    and add it to the matrix to see if it is returning the expected values.

    • jn32324's avatar
      jn32324
      New Member
      8 years ago

      MarkS When I added that measure, it incorrectly returned the sum total for that year, not the previous year.

       

      However, when I changed it to this measure with previuosyear() as the function, it correctly displayed the previous year.

      Prev_year = CALCULATE(
			SUM(fact[amount]),
			PREVIOUSYEAR(CalendarDate[Date])
		)