Forum Discussion
Y-o-Y Line Chart: Multiple Years
Hi all,
I am facing the same issue pankajdhillon was having: http://community.powerbi.com/t5/Desktop/Year-over-Year-in-line-chart/td-p/13691. However, the proposed solution does not solve my problem.
I want to show the daily y-o-y growth rate of number of transactions for several years on the same chart. First, I calculate the total number of transactions as a new measure:
Total Volume = CALCULATE(SUM(CombinedData[Volume]))
Using this new measure, I create a measure to obtain sum of transactions for previous year:
Volume LY = CALCULATE([Total Volume], DATEADD('CombinedData'[Sales ISODate],-1, YEAR))
Finally, I obtain the y-o-y growth rate:
Volume % = DIVIDE(([Total Volume]-[Volume LY]),[Volume LY], BLANK())
Now, if I put Volume % and Transaction Day (mm-dd) on the same chart, I get an error saying that "Function DATEADD only works with contiguous date selections". The same happens, if I use SAMEPERIODPREVIOUSYEAR function to calculate Volume LY.
The chart does not give error but stays blank (because Volume LY is not being calculated), if I calculate Volume LY like this:
Volume LY = SUMX(CombinedData, CALCULATE([Total Volume], DATEADD(CombinedData[Sales ISODate], -1, YEAR)))
Could you please help me understand where the problem is? Thanks a lot in advance!
Hi GabrieleVLN,
You'd better create a Calendar table, create a relationship between CombinedData and Calendar table. Then replacte 'CombinedData'[Sales ISODate] to 'Calendar[Date]' when you create measure. Please refer to the similar post.Volume LY = CALCULATE([Total Volume], DATEADD('Calendar'[Date],-1, YEAR))
Then create Transaction Day (mm-dd) using 'Calendar[Date]' in Calendar table. And create Line chart like the thread.
If you have other issues, please feel free to ask.Best Regards,
Angelia
1 Reply
- v-huizhn-msft
Microsoft Employee
Hi GabrieleVLN,
You'd better create a Calendar table, create a relationship between CombinedData and Calendar table. Then replacte 'CombinedData'[Sales ISODate] to 'Calendar[Date]' when you create measure. Please refer to the similar post.Volume LY = CALCULATE([Total Volume], DATEADD('Calendar'[Date],-1, YEAR))
Then create Transaction Day (mm-dd) using 'Calendar[Date]' in Calendar table. And create Line chart like the thread.
If you have other issues, please feel free to ask.Best Regards,
Angelia