Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
I am trying to calculate the percentage difference between years (based on what's selected in the filters).
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
Solved! Go to 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:
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
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
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.
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:
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
ay ay ay ay Heather
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
User | Count |
---|---|
93 | |
90 | |
90 | |
81 | |
49 |
User | Count |
---|---|
156 | |
145 | |
104 | |
72 | |
55 |