p.p. change - Exclude where no data for current year

[Anonymous]

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

[Anonymous]

Link to pbix on dropbox (select the download button)

Test pbix file

[v-cherch-msft · Accepted solution]

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

[Anonymous]

@v-cherch-msft 

Thanks so much for this, works like a charm 🙂 much appreciated. 

Keelin