Forum Discussion
p.p. change - Exclude where no data for current year
Hi All,
I have a test PBI file where I am encountering an issue I cant quite solve. I am calculating the p.p. difference between scores across two years and want to exclude any record for which there is no data for the most recent year (filtered).
The formula I am using is below, I need to exclude any record where there is no record for Year_New
Score p.p. YoY% =
IF(
ISFILTERED('TestData'[Year_New]),
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(
[Calc],
DATEADD('TestData'[Year_New].[Date], -1, YEAR)
)
RETURN
[Calc] - __PREV_YEAR
)pbix file link below
Hi Anonymous
You may add IF conditon like below:
Score p.p. YoY% =
IF (
ISFILTERED ( 'TestData'[Year_New] ),
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 ( [Calc], DATEADD ( 'TestData'[Year_New].[Date], -1, YEAR ) )
RETURN
IF ( [Calc] <> BLANK (), [Calc] - __PREV_YEAR )
)Regards,
Cherie
3 Replies
v-cherch-msftMicrosoft Employee
Hi Anonymous
You may add IF conditon like below:
Score p.p. YoY% =
IF (
ISFILTERED ( 'TestData'[Year_New] ),
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 ( [Calc], DATEADD ( 'TestData'[Year_New].[Date], -1, YEAR ) )
RETURN
IF ( [Calc] <> BLANK (), [Calc] - __PREV_YEAR )
)Regards,
Cherie