Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
heathernicole
Continued Contributor
Continued Contributor

percentage difference with more than two years filtered

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

SampleSample
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

~heathernicoale
1 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... Smiley Tongue

 

Here's what I've ended up with:

Correct.png

 

 

~heathernicoale

View solution in original post

9 REPLIES 9
LivioLanzo
Solution Sage
Solution Sage

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

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

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

 

MarkAsDateTable.png

~heathernicoale

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

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

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

~heathernicoale

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

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

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

 

sampleData2.png

~heathernicoale

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... Smiley Tongue

 

Here's what I've ended up with:

Correct.png

 

 

~heathernicoale

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

ay ay ay ay Heather Smiley Wink

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.