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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.