Forum Discussion

ChrisPBI's avatar
ChrisPBI
Advocate III
10 years ago
Solved

Display Percent Changes alongside a Line Chart

Hello everyone,

 

is it possible to display percent changes alongside a line chart?

 

That is my use case:

 

 

 

 

 

 

 

 

 

 

 

 

 

I would like to display this in the graph in between the sum values.

 

E.g. from 2009 with 2618 to 2010 with 6127.

 

This functionality would be sufficent for consecutive years .

 

Thanks and Regards,

Chris

 

Need Help
  • Eric_Zhang's avatar
    Eric_Zhang
    9 years ago

    ChrisPBI

     

    1. Create a calendar table.
      calendar TABLE = CALENDAR("2001-01-01","2016-12-31")
    2. Map one:many relationship.
    3. Change your measure to
      LastYearAmount = CALCULATE(SUM('GGE Amounts'[GGE in kg CO2e]),PREVIOUSYEAR('calendar TABLE'[Date]))
    4. Done


     

    Check the details in the attached pbix, the unzip password is sent via private message.

     

     

10 Replies

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    9 years ago

    ChrisPBI

     

    A line and column combo chart would be a better visual to show. In this case, the key is the PREVIOUSYEAR function. Check more details in the attached pbix.

     

    Percent Change =
VAR LastYearAmt =
    CALCULATE ( SUM ( Table1[GGE in kg CO2e] ), PREVIOUSYEAR ( Table1[Date] ) )
RETURN
    IF (
        ISBLANK ( LastYearAmt ),
        0,
        ( SUM ( Table1[GGE in kg CO2e] ) - LastYearAmt )
            / LastYearAmt
    )

     

     

    • ChrisPBI's avatar
      ChrisPBI
      Advocate III
      9 years ago

      Hi Eric,

       

      that is a very cool possibility.

       

      I can't get it work with my setting.

       

      You have my .pbix, could you maybe try it?

       

      I tried: LastYearAmount = CALCULATE(SUM('GGE Amounts'[GGE in kg CO2e]);PREVIOUSYEAR('Models'[Release Date]))

       

      I don't get an error with that, but it's not given an result.

       

      I would expect to get this table (from your example) but with my dates:

       

       

       

       

       

       

       

       

       

       

       

       

       

       

      Best would be to only have the years in the first row.

       

      Therefore, I tried it with my year extraction: YEAR('Models'[Release Date]), but that's not working at all.

       

      Thanks and Regards,

      Chris

      • Eric_Zhang's avatar
        Eric_Zhang
        Microsoft Employee
        9 years ago

        ChrisPBI

         

        1. Create a calendar table.
          calendar TABLE = CALENDAR("2001-01-01","2016-12-31")
        2. Map one:many relationship.
        3. Change your measure to
          LastYearAmount = CALCULATE(SUM('GGE Amounts'[GGE in kg CO2e]),PREVIOUSYEAR('calendar TABLE'[Date]))
        4. Done


         

        Check the details in the attached pbix, the unzip password is sent via private message.

         

         

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi!

      I'm having the same problem as in this post. I checked your PBIX file and the solution seems to do what it should but in your example, you have only one entry per year:

      In my case, I have several entries per month in each year. So, what I get is this:

      Is showing me the trend along each month from (sept) 2013 to (July) 2020. How can I make it appear only per the year 2013 to 2020?

       

      I hope you could answer this post as I know is pretty old. 

      Thanks in advance!