Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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]) )
Hi,
Try this
=CALENDAR(MIN('fact'[processing_date]),MAX('fact'[processing_date]))
=YEAR(CalendarDate[Date])
=SUM('fact'[amount])/CALCULATE(SUM('fact'[amount]),PREVIOUSYEAR(CalendarDate[Date]))
Hope this helps.
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]) )
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%
2015 | 1,000 | 0% |
2016 | 2,000 | 0% |
2017 | 3,000 | 0% |
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |