Quick Measure for change showing 0.00%

[abertolett]

I can't get the quick measure to work for YOY (and probably any other unit).

CTR= 
IF(
ISFILTERED('Date'[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([CTR], DATEADD('Date'[Date].[Date], -1, YEAR))
RETURN
DIVIDE([CTR] - __PREV_YEAR, __PREV_YEAR)
)

If I try to use other date values, it starts throwing errors about using the Date Hierarchy.

I have a filter context displaying the rolled up CTR avgs for 2016, 2017, 2018.

I'm able to "hardcode" a filter like '2016' but I can't make it change along with the context of the visual.

[v-xjiin-msft · Accepted solution]

@abertolett

Based on your measure, you are using DateAdd() function. Right?

By using DateAdd() function, you can refer to the MS article and will find out that “The result table includes only dates that exist in the dates column.” It also means that this function will only return the date which is existed in the original date column.

Please refer:

So, in your scenario, the issue that your measure is not working maybe related to the DateAdd() function is returning blank values. Please verify this.

And if you want to subtract a single year, you can try following measure:

MinusOneYear =
IFERROR (
    DATE ( YEAR ( TableName[Date] ) - 1, MONTH ( TableName[Date] ), DAY ( TableName[Date] ) ),
    BLANK ()
)

Thanks,
Xi Jin.