Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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

@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
Ashish_Mathur
Super User
Super User

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/
MarkS
Resolver IV
Resolver IV

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.

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

 

MarkS
Resolver IV
Resolver IV

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?

 

@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%

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.