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.

     

     

Eric_Zhang's avatar
Eric_Zhang
Microsoft Employee
10 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
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.