Reply
Frequent Visitor
Posts: 4
Registered: ‎03-16-2017
Accepted Solution

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!


Accepted Solutions
Community Support Team
Posts: 3,609
Registered: ‎09-27-2016

Re: Y-o-Y Line Chart: Multiple Years

[ Edited ]

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

View solution in original post


All Replies
Community Support Team
Posts: 3,609
Registered: ‎09-27-2016

Re: Y-o-Y Line Chart: Multiple Years

[ Edited ]

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