cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
abertolett Frequent Visitor
Frequent Visitor

Quick Measure for change showing 0.00%

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.

1 ACCEPTED SOLUTION

Accepted Solutions
v-xjiin-msft Super Contributor
Super Contributor

Re: Quick Measure for change showing 0.00%

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

 

C.PNG

 

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.

View solution in original post

1 REPLY 1
v-xjiin-msft Super Contributor
Super Contributor

Re: Quick Measure for change showing 0.00%

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

 

C.PNG

 

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.

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 101 members 1,424 guests
Please welcome our newest community members: