cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jn32324
New Member

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.

1 ACCEPTED SOLUTION

Accepted Solutions
jn32324
New Member

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

@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])
		)

 

View solution in original post

5 REPLIES 5
MarkS Resolver IV
Resolver IV

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

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
New Member

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

@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 Resolver IV
Resolver IV

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

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.

Super User IV
Super User IV

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

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
jn32324
New Member

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

@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])
		)

 

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors