percentage difference with more than two years filtered

[heathernicole]

I am trying to calculate the percentage difference between years (based on what's selected in the filters).

Sample
The yoy% measure I have is not calculating properly, but I'm not sure why.

YoY% = 
IF(
	ISFILTERED('Calendar - Transaction Date'[Transaction 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(
			[Outgoing Total Sales],
			DATEADD('Calendar - Transaction Date'[Transaction Date].[Date], -1, YEAR)
		)
	RETURN
		DIVIDE([Outgoing Total Sales] - __PREV_YEAR, __PREV_YEAR)
)

All I need it to do is dynamically calculate the percentage change (negative or postive) between each year (i.e. between 2015 and 2016, between 2016 and 2017, between 2017 and 2018). Or whatever years are selected in the filter.

Any help is appreciated. 

Thanks!

~h

[LivioLanzo]

Hi Heather, is your Calendar table marked as a Date table? 

[heathernicole]

I thought so - but I got this when I tried to make it one. It's a table from QuickBooks

[LivioLanzo]

This could be the source of your problem.

You would need to create a proper calendar table without any gaps in the dates and then mark it as data table.

You can create it either with Power Query or DAX. I would choose Power Query

[heathernicole]

I got rid of the gaps - made it a date table and then it messed up visuals. They couldn't display at all because they needed a date hiearchy. Which I could no longer create after making the table a date table. 

Any ideas?

~h

[LivioLanzo]

You can recreate the hierarchy easily: https://www.youtube.com/watch?v=q8WDUAiTGeU

[heathernicole]

Ok - got the date table setup finally and the issue fixed. However - I'm still showing the same values.

[heathernicole · Accepted solution]

Error on my part with the formula - I thought it was being calculate wrong but wasn't. I had the calculation for percentage OF - not percentage difference... 

Here's what I've ended up with:

[v-cherch-msft]

Hi @heathernicole

Please share your data sample file for us to check the issue. You can upload it to OneDrive or Dropbox and post the link here.

Regards,

Cherie

[LivioLanzo]

ay ay ay ay Heather