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 ...
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.

     

     

ChrisPBI's avatar
ChrisPBI
Advocate III
10 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.

 

 

  • ChrisPBI's avatar
    ChrisPBI
    Advocate III
    9 years ago

    Hi Eric,

     

    thank you very much, that works!

     

    But it is not possible without the calender table, right?

     

    Why can't I use my year column for that?

     

    Regards,

    Chris

    • Eric_Zhang's avatar
      Eric_Zhang
      Microsoft Employee
      9 years ago

      ChrisPBI

       

      The function PREVIOUSYEAR/MONTH and some other timeintelligence functions should be on contiguous, nonrepeating dates.

      • Patt's avatar
        Patt
        Frequent Visitor
        8 years ago

        completed line chartDatasetI tried to use the example above to get percent change to calculate for my data and couldn't get it to work for the life of me.