Y-o-Y Line Chart: Multiple Years

[GabrieleVLN]

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!

[v-huizhn-msft · Accepted solution]

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