Forum Discussion

GabrieleVLN's avatar
GabrieleVLN
Frequent Visitor
9 years ago
Solved

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's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft 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